按ROUTE_ID、CUST_ID、PROD_ID合并日期CASUAL字段的SQL求助

你需要对原查询中的日期字段使用聚合函数，并按ROUTE_ID、CUST_ID、PROD_ID进行分组，以此合并相同分组下各日期的CASUAL值。修改后的SQL如下：

SELECT
    ROUTE_ID,
    CUST_ID,
    PROD_ID,
    SUM(CASE WHEN DAY = '1' THEN CASUAL ELSE 0 END) AS sun_draw,
    SUM(CASE WHEN DAY = '2' THEN CASUAL ELSE 0 END) AS mon_draw,
    SUM(CASE WHEN DAY = '3' THEN CASUAL ELSE 0 END) AS tues_draw,
    SUM(CASE WHEN DAY = '4' THEN CASUAL ELSE 0 END) AS wed_draw,
    SUM(CASE WHEN DAY = '5' THEN CASUAL ELSE 0 END) AS thur_draw,
    SUM(CASE WHEN DAY = '6' THEN CASUAL ELSE 0 END) AS fri_draw,
    SUM(CASE WHEN DAY = '7' THEN CASUAL ELSE 0 END) AS sat_draw
FROM
    SUPPLIES 
WHERE
    PUBDATE BETWEEN DATEADD(DAY, -7, GETDATE()) AND GETDATE() 
    AND CASUAL > 0 
    AND CUST_ID = 17259
GROUP BY
    ROUTE_ID,
    CUST_ID,
    PROD_ID

关键说明： #

• 用SUM()函数包裹每个CASE表达式，将同一分组（相同ROUTE_ID、CUST_ID、PROD_ID）下对应日期的CASUAL值累加合并。
• 将ROUTE_ID加入SELECT列表和GROUP BY子句，满足按该字段分组的要求。
• 把原查询中的ELSE '0'改为ELSE 0（去掉引号），避免字符串转数值的潜在问题，确保聚合计算正常。

内容的提问来源于stack exchange，提问作者Matt