SELECT SUM(`Total sale`)
FROM `sales details`
WHERE `Day` = "Sunday"
AND `Shop ID` = (
SELECT `Shop ID`
FROM `shops`
WHERE `shops`.`Area Code` = (
SELECT `Area Code`
FROM `distributors_areas`
WHERE `distributors_areas`.`Regional_Manager` = (
SELECT `Name`
FROM `regional managers`
WHERE `regional managers`.`ID` = "REG_02"
)
)
)
我想得到Total sale
的总和,其中白天是星期天,商店在区域经理"REG_02"的行政区域内。因此,我将周日销售的商店ID与属于"RM_02"的那些地区的商店ID进行了比较。 shops
表中有区域经理的姓名,因此我将他们与表中regional managers
姓名进行比较,得到"RM_002"的名称。
简单的方法是使用 IN
:
SELECT SUM(sd.`Total sale`)
FROM `sales details` sd
WHERE sd.`Day` = 'Sunday' AND
sd.`Shop ID` IN
(SELECT s.`Shop ID`
FROM `shops` s
WHERE s.`Area Code` IN
(SELECT da.`Area Code`
FROM `distributors_areas` da
WHERE da.`Regional_Manager` IN
(SELECT rm.`Name`
FROM `regional managers` rm
WHERE rm.`ID` = 'REG_02'
)
)
);
更明智的方法只需使用JOIN
:
SELECT SUM(sd.`Total sale`)
FROM `sales details` sd JOIN
`shops` s
ON sd.`Shop ID` = s.`Shop ID` JOIN
`distributors_areas` da
ON s.`Area Code` = da.`Area Code` JOIN
`regional managers` rm
ON da.Regional_Manager = rm.Name
WHERE sd.Day = 'Sunday' AND
rm.ID = 'REG_02';
笔记:
- 使用作为表名称缩写的表别名。 这使查询更易于读取和写入。
- SQL 标准字符串分隔符是单引号。 你应该使用它。 双引号有多种用途,因此它们不明确。
- 无需对仅由字母、数字和下划线的标识符使用反引号。 它们只会使查询更难编写和读取。
- 避免在名称中使用空格等字符,因此无需对名称进行转义。
如果你只需要一行,那么你可以使用限制 1
SELECT SUM(`Total sale`)
FROM `sales details`
WHERE `Day` = "Sunday" AND `Shop ID` = (
SELECT `Shop ID`
FROM `shops` WHERE `shops`.`Area Code` = (
SELECT `Area Code`
FROM `distributors_areas`
WHERE `distributors_areas`.`Regional_Manager` = (
SELECT `Name`
FROM `regional managers`
WHERE `regional managers`.`ID` = "REG_02"
ORDER BY name
LIMIT 1
)
ORDER by `Area Code`
LIMIT 1
)
)
如果您需要多个结果,则应使用 IN
SELECT SUM(`Total sale`)
FROM `sales details`
WHERE `Day` = "Sunday" AND `Shop ID` IN (
SELECT `Shop ID`
FROM `shops` WHERE `shops`.`Area Code` IN (
SELECT `Area Code`
FROM `distributors_areas`
WHERE `distributors_areas`.`Regional_Manager` IN = (
SELECT `Name`
FROM `regional managers`
WHERE `regional managers`.`ID` = "REG_02"
)
)
)
但是查看您的代码似乎您可以使用内部连接来避免子查询
SELECT SUM(`Total sale`)
FROM `sales details`
INNER JOIN `shops` ON `shops`.`Shop ID` = `sales details`. `Shop ID`
INNER JOIN distributors_areas` ON distributors_areas`.`Area Code` = `shops`.`Area Code`
INNER JOIN `regional managers` ON `regional managers` .`Name` = distributors_areas`.`Regional_Manager`
and `regional managers`.`ID` = "REG_02"
哪里sales details
.Day
= "星期日">