我sales在 mysql 中的表如下所示：
ID Date Quantity
00000001 01/01/2017 5
00000002 02/01/2017 25
00000003 01/01/2017 2
00000001 04/01/2017 58
00000005 01/01/2017 8
00000001 10/01/2017 2
我想获得每种产品的每周销售额（在本例中为第 1 周）。以下是我的查询
SELECT ID,
IF (YEARWEEK(Date) = '201401', SUM(Quantity), NULL) AS WEEK1
FROM sales
GROUP BY ID
它返回以下错误：
#1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sales.registerDate' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
我也使用过 CASE 语句，但它对我来说效果不佳。
SELECT ID,
CASE WHEN (YEARWEEK(Date) = '201401' THEN SUM(Quantity) END AS WEEK1
FROM sales
GROUP BY ID
它返回错误
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'THEN SUM(Quantity) END) WEEK1
FROM sales
GROUP BY ID' at line 2
预期的结果格式是
ID week1
00000001 63
00000002 25
00000003 2
00000005 8
获得预期结果的正确方法是什么？
尝试总结 case 语句，例如：
SELECT ID,
SUM(CASE WHEN YEARWEEK(Date) = '201401' THEN Quantity END) AS WEEK1
FROM sales
GROUP BY ID
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句