gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-11-29 01:25:55 25 4
gpt4 key购买 nike

我的数据库的相关部分如下所示(MS Visio,我知道我很可怜:D): Description?

我需要提取一个包含类别中所有项目以及 bundle 的列表。所以我必须使用UNIONUNION 的第一部分供您引用(因为它设置 UNION 第二部分中 SELECT 的数据格式;请注意, >? 表示参数在 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
)

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

我的尝试:

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

最佳答案

您似乎遇到了一些语法问题。您的代码进行了一些更改。有关详细信息,请参阅查询中的评论。

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
);

关于mysql - 需要棘手的 sql 查询,查找子查询的总和,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/55764219/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com