mysql - 如何从子查询中获取多列 sql 语句 mysql

Question

实际上，我为我的问题找到了许多解决方案，但我不明白如何应用于我的问题！

我的问题是我有以下 SQL 语句：

SELECT
                                `tblacc`.`AccID` AS xAccID,
                                `tblacc`.`AccName` AS xAccName,
                                `tblprod`.`ProductID` AS xProdID,
                                `tblprod`.`ProductName` AS xProdName,
                                (
                                SELECT
                                    tblproductsprices.PriceID
                                FROM
                                    tblproductsprices
                                WHERE
                                    tblproductsprices.PriceProductID = `tblprod`.`ProductID` AND `tblproductsprices`.`PriceResoID` = `tblacc`.`AccID`
                            ) AS xPriceID,
                            (
                            SELECT
                                tblproductsprices.PriceValue
                            FROM
                                tblproductsprices
                            WHERE
                                tblproductsprices.PriceProductID = `tblprod`.`ProductID` AND `tblproductsprices`.`PriceResoID` = `tblacc`.`AccID`
                            ) AS xPriceValue,
                            (
                            SELECT
                                tblproductsprices.PricePallet
                            FROM
                                tblproductsprices
                            WHERE
                                tblproductsprices.PriceProductID = `tblprod`.`ProductID` AND tblproductsprices.PriceResoID = `tblacc`.`AccID`
                            ) AS xPalletPrice
                            FROM
                                tblaccounts tblacc,
                                tblproducts tblprod
                            WHERE
                                tblacc.`AccCat` = 'agent'
                            ORDER BY
                                tblacc.AccName,
                                tblprod.ProductName

我需要将以下部分替换为一个部分：

(
                                SELECT
                                    tblproductsprices.PriceID
                                FROM
                                    tblproductsprices
                                WHERE
                                    tblproductsprices.PriceProductID = `tblprod`.`ProductID` AND `tblproductsprices`.`PriceResoID` = `tblacc`.`AccID`
                            ) AS xPriceID,
                            (
                            SELECT
                                tblproductsprices.PriceValue
                            FROM
                                tblproductsprices
                            WHERE
                                tblproductsprices.PriceProductID = `tblprod`.`ProductID` AND `tblproductsprices`.`PriceResoID` = `tblacc`.`AccID`
                            ) AS xPriceValue,
                            (
                            SELECT
                                tblproductsprices.PricePallet
                            FROM
                                tblproductsprices
                            WHERE
                                tblproductsprices.PriceProductID = `tblprod`.`ProductID` AND tblproductsprices.PriceResoID = `tblacc`.`AccID`
                            ) AS xPalletPrice

以相同的条件获取一个子查询中的所有列？

标签： mysqlsql

Answer 1

I don't know your data schema. Do you try making a CROSS JOIN (first) and a LEFT JOIN with table tblproductsprices?

SELECT
    `tblacc`.`AccID` AS xAccID,
    `tblacc`.`AccName` AS xAccName,
    `tblprod`.`ProductID` AS xProdID,
    `tblprod`.`ProductName` AS xProdName,
    tblproductsprices.PriceID AS xPriceID,
    tblproductsprices.PriceValue AS xPriceValue,
    tblproductsprices.PricePallet AS xPalletPrice
    
    FROM tblaccounts tblacc

    CROSS JOIN tblproducts tblprod
    
    LEFT JOIN tblproductsprices ON tblproductsprices.PriceProductID = `tblprod`.`ProductID` AND `tblproductsprices`.`PriceResoID` = `tblacc`.`AccID`
    
    WHERE tblacc.`AccCat` = 'agent'

    ORDER BY tblacc.AccName, tblprod.ProductName;