我不知道该怎么回答这个问题。我有一张这样的桌子（简体）：
Date Weight kg
-------------------
2012-04-16 12.4
2012-04-17 9.6
2012-04-16 5.4
2012-04-18 2.8
2012-04-16 4.5
... ...
我希望查询返回此结果：
Week.no. <3kg 3-7kg >7kg
----------------------------
16 2.8 9.9 22.0
... ... ... ....
这就是我目前所拥有的：
SELECT *, CONCAT(WEEK(`Date`)) AS Week, SUM(`Weight`) AS TotalWeight,
(SELECT SUM(`Weight`) FROM tbl_fangster WHERE `Weight` < 3 AND
`Date` >= '2012-04-01 00:00:00' AND `Date` <= '2012-04-30 00:00:00'
AND `Species` = 'Salmon' ) AS SumSmall
FROM tbl_fangster
WHERE `Date` >= '2012-04-01 00:00:00'
AND `Date` <= '2012-04-30 00:00:00'
AND `Species` = 'Salmon'
GROUP BY CONCAT(WEEK(`Date`))
但是sumsmal在每一行返回相同的数字，即每周的sumsmal总数而不是sumsmal。我试图将GROUP子句复制粘贴到子查询中，但没有成功。
最佳答案
使用CASE语句处理各种条件。
SELECT *, CONCAT(WEEK(`Date`)) AS Week, SUM(`Weight`) AS TotalWeight,
SUM(CASE WHEN Weight < 3 THEN Weight ELSE 0 END) AS SumSmall,
SUM(CASE WHEN Weight >= 3 AND Weight <= 7 THEN Weight ELSE 0 END) AS SumMedium,
SUM(CASE WHEN Weight > 7 THEN Weight ELSE 0 END) AS SumLarge
FROM tbl_fangster
WHERE `Date` >= '2012-04-01 00:00:00'
AND `Date` <= '2012-04-30 00:00:00'
AND `Species` = 'Salmon'
GROUP BY CONCAT(WEEK(`Date`))
关于mysql - MySQL嵌套SELECT，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/10179821/