mysql - 需要棘手的 sql 查询，找到子查询的总和

Question

我的数据库的相关部分如下所示（MS Visio，我知道我很可悲：D）：

我需要提取一个列表，其中包含一个类别中的所有项目以及捆绑包。所以我必须使用UNION. a 的第一部分UNION供您参考（因为它设置了SELECT第二部分中的数据格式UNION；请注意，它?表示参数进入的位置node-mysql）：

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
        ) 

所以我想展示我的捆绑包，就好像它们也是项目一样，具有所有相同的字段等。

我的尝试：

SELECT `ID`, `Name`, `Description`, `PictureID`, 
      (
       SELECT SUM( // Here SQL indicates a syntax problem
                  SELECT `ItemAmount`*`PriceModifier`*(
                                         SELECT `SellingPrice` 
                                         FROM `Item` 
                                         WHERE `ID`=`BundleItem`.`ItemID`
                                         ) 
                   FROM `BundleItem` WHERE `BundleID`=`Bundle`.`ID`
                 )
      ) AS `SellingPrice`,
      (
        SELECT SUM(
                   SELECT `ItemAmount`*(
                                        SELECT `Cost` 
                                        FROM `Item` 
                                        WHERE `ID`=`BundleItem`.`ItemID`
                                       )
                    FROM `BundleItem` WHERE `BundleID`=`Bundle`.`ID`
                  )
      ) AS `Cost`, 
      1 AS `Bundle`
FROM `Bundle` 
WHERE `ID` IN (
               SELECT `BundleID` 
               FROM `BundleCategory` 
               WHERE `CategoryID`=?
              )
//No need to check bundles for stock due to business logic

我有一个模糊的想法，我把这件事复杂化了，但不幸的是，我不能指望它。

任何建议都将非常受欢迎，并提前感谢您抽出宝贵的时间。<3

样本数据：

Fields of no interest like "Description"/"PictureID"/"SupplierID" will be omitted
for the relevant parts to fit on screen

**Bundle**
ID  Name            Description             PictureID
1   Valentine Pack  Blah-blah tasty buy me  imgur link in text

**Item**
ID  Name               SellingPrice  Cost  CategoryID
1   Movie Ticket       10            2     24
2   Box of Chocolates  5             1     4
3   Teddy Bear         15            3     2
4   Roses              10            4     8

**Stock**
ItemID  CityID  Quantity  IsLimitLess 
1       1       25        false
1       2       11        false
2       1       84        false
3       1       33        false
4       1       1         true
4       3       1         true

**BundleItem**
BundleID  ItemID  ItemAmount  PriceModifier
1         1       2           1.25
1         2       1           1
1         3       1           1
1         4       5           0.75

**BundleCategory** (bundle for marketing reasons can appear in different
categories depending on its contents)
BundleID  CategoryID
1         4 //Sweets
1         2 //Toys
1         8 //Flowers

期望的输出：（用于搜索 CityID 1、CategoryID 8、Flowers）

ID  Name    (Descr/PicID)    SellingPrice Cost         Bundle

4   Roses                    10           4            false

1   Valentine Pack           82.5         28           true
                           /*2*10*1.25+   2*2+  <movie
                             1*1*5+       1*1+  <chocolate
                             1*1*15+      3*1+  <teddy bear
                             5*0.75*10    5*4   <roses */

用户建议的解决方案 根据@drakin8564 的建议，我尝试做

SELECT `ID`, `Name`, `Description`, `PictureID`, 
      (
       SELECT SUM(( 
                  SELECT `ItemAmount`*`PriceModifier`*(
                                         SELECT `SellingPrice` 
                                         FROM `Item` 
                                         WHERE `ID`=`BundleItem`.`ItemID`
                                         ) 
                   FROM `BundleItem` WHERE `BundleID`=`Bundle`.`ID`
                        ))
      ) AS `SellingPrice`,
      (
        SELECT SUM((
                   SELECT `ItemAmount`*(
                                        SELECT `Cost` 
                                        FROM `Item` 
                                        WHERE `ID`=`BundleItem`.`ItemID`
                                       )
                    FROM `BundleItem` WHERE `BundleID`=`Bundle`.`ID`
                  ))
      ) AS `Cost`, 
      1 AS `Bundle`
FROM `Bundle` 
WHERE `ID` IN (
               SELECT `BundleID` 
               FROM `BundleCategory` 
               WHERE `CategoryID`=8
              )

退货

(1242): Subquery returns more than 1 row. 

即使我尝试也会发生这种情况SELECT SUM((SELECT ID FROM Item))。诡异的。我评论了其他解决方案，说明它们的效果如何。我感谢所有参与此活动的人。<3

标签： mysqlsql

Answer 1

看起来您遇到了一些语法问题。您的代码进行了一些更改。有关详细信息，请参阅查询中的注释。

http://sqlfiddle.com/#!9/ee0725/16

SELECT `ID`, `Name`, `Description`, `PictureID`, 
                  (SELECT SUM(`ItemAmount`*`PriceModifier`*( -- changed order of SELECT and SUM; removed extra SELECT; fixed Parens
                                         SELECT `SellingPrice` 
                                         FROM `Item` 
                                         WHERE `ID`=`BundleItem`.`ItemID`
                                         ))
                   FROM `BundleItem` WHERE `BundleID`=`Bundle`.`ID`)
       AS `SellingPrice`,
                   (SELECT SUM(`ItemAmount`*( -- changed order of SELECT and SUM; removed extra SELECT; fixed Parens
                                        SELECT `Cost` 
                                        FROM `Item` 
                                        WHERE `ID`=`BundleItem`.`ItemID`
                                       ))
                    FROM `BundleItem` WHERE `BundleID`=`Bundle`.`ID`)
       AS `Cost`, 
      1 AS `Bundle`
FROM `Bundle` 
WHERE `ID` IN (
               SELECT `BundleID` 
               FROM `BundleCategory` 
               WHERE `CategoryID`=8
              );