编写复杂SQL查询：合并商品与捆绑包数据并计算金额

嘿，我来帮你搞定这个查询问题！你遇到的1242 - 子查询返回多于一行错误，是因为嵌套子查询的写法不对——你在SUM里直接嵌套了一个会返回多行的子查询，SUM无法处理多行结果，所以报错了。咱们换用JOIN关联的方式来计算捆绑包的价格和成本，会更简洁高效，也能解决语法问题。

完整的UNION查询语句 #

-- 第一部分：查询指定分类下有库存的商品
SELECT 
    `ID`, 
    `Name`, 
    `Description`, 
    `PictureID`, 
    `SellingPrice`, 
    `Cost`, 
    0 AS `Bundle` 
FROM `Item` 
WHERE 
    `CategoryID`=? 
    AND `ID` IN ( 
        SELECT `ItemID` 
        FROM `Stock` 
        WHERE `CityID`=? 
        AND (`IsLimitless`=1 OR `Quantity`>0) -- 注意加括号修正逻辑优先级
    )

UNION ALL -- 用UNION ALL更高效，无需额外去重

-- 第二部分：查询指定分类下的捆绑包，计算对应价格和成本
SELECT 
    b.`ID`, 
    b.`Name`, 
    b.`Description`, 
    b.`PictureID`, 
    -- 计算捆绑包总售价：商品数量*价格系数*商品售价 求和
    SUM(bi.`ItemAmount` * bi.`PriceModifier` * i.`SellingPrice`) AS `SellingPrice`,
    -- 计算捆绑包总成本：商品数量*商品成本 求和
    SUM(bi.`ItemAmount` * i.`Cost`) AS `Cost`,
    1 AS `Bundle`
FROM `Bundle` b
JOIN `BundleItem` bi ON b.`ID` = bi.`BundleID`
JOIN `Item` i ON bi.`ItemID` = i.`ID`
WHERE b.`ID` IN (
    SELECT `BundleID` FROM `BundleCategory` WHERE `CategoryID`=?
)
GROUP BY b.`ID`, b.`Name`, b.`Description`, b.`PictureID`; -- 按捆绑包分组聚合

关键修正点说明 #

1. 逻辑优先级修正：在商品查询的Stock条件里，给IsLimitless=1 OR Quantity>0加了括号——因为AND的优先级比OR高，原来的写法会变成(CityID=? AND IsLimitless=1) OR Quantity>0，这不符合你的业务逻辑，加括号后才是正确的CityID=? AND (IsLimitless=1 OR Quantity>0)。

2. 捆绑包计算方式优化：

   • 用JOIN关联Bundle、BundleItem和Item表，直接在聚合函数里计算每个捆绑包的总售价和总成本，彻底避免嵌套子查询的多行返回问题。
   • 通过GROUP BY按捆绑包的唯一标识（ID、名称等字段）分组，确保每个捆绑包只返回一行结果，完全匹配UNION要求的字段格式。

3. 使用UNION ALL：如果你的商品和捆绑包ID不会重复，用UNION ALL比UNION性能更好，因为UNION会额外做去重操作，这里完全不需要。

验证示例数据 #

当你传入参数CityID=1、CategoryID=8时，查询结果会和你期望的完全一致：

• 商品Roses（ID=4）会被查出，因为Stock里CityID=1且IsLimitless=1；
• 捆绑包Valentine Pack（ID=1）会被查出，计算后的售价是2*10*1.25 + 1*1*5 + 1*1*15 +5*0.75*10 = 82.5，成本是2*2 +1*1 +1*3 +5*4=28，完美符合预期。

内容的提问来源于stack exchange，提问作者Cowbless