如何按周拆分同星期几的销售数据，避免SUM合并结果？

问题原因 #

原查询仅通过DATEPART(WEEKDAY, Date)分组，导致不同周的同一星期几数据被合并求和。要区分不同周的数据，必须在分组时加入周标识。

修改后的查询语句 #

SELECT
    CASE WHEN weekdays = 1 THEN Total_Sales ELSE 0 END as Sunday,
    CASE WHEN weekdays = 2 THEN Total_Sales ELSE 0 END as Monday,
    CASE WHEN weekdays = 3 THEN Total_Sales ELSE 0 END as Tuesday,
    CASE WHEN weekdays = 4 THEN Total_Sales ELSE 0 END as Wednesday,
    CASE WHEN weekdays = 5 THEN Total_Sales ELSE 0 END as Thursday,
    CASE WHEN weekdays = 6 THEN Total_Sales ELSE 0 END as Friday,
    CASE WHEN weekdays = 7 THEN Total_Sales ELSE 0 END as Saturday
FROM
    (SELECT 
         DATEPART(WEEK, date) AS WeekNum,
         DATEPART(WEEKDAY, date) AS Weekdays,
         SUM(sales) AS Total_Sales
     FROM Sales
     WHERE CAST(Date AS date) BETWEEN '2023-03-01' AND '2023-03-10'
     GROUP BY DATEPART(WEEK, date), DATEPART(weekday, Date)) X
ORDER BY WeekNum

关键修改点 #

• 子查询新增DATEPART(WEEK, date)获取周编号，用于区分不同周的记录
• GROUP BY同时按周编号和星期几分组，确保同一星期几的跨周数据被分开统计
• 外层添加ORDER BY WeekNum，保证结果按周顺序展示，匹配期望格式

修改后结果 #

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