如何修改SQL查询补全无记录的星期时段并填充0？

核心思路是先生成所有星期-小时的完整组合作为基础表，再左连接实际的收据统计数据，无记录的时段用COALESCE或ISNULL填充0。

具体实现步骤 #

1. 生成完整星期列表
   先定义包含周一到周日的固定列表，同时加入排序编号避免字符串排序混乱：

   WITH WeekDays AS (
       SELECT 1 AS WeekDayNum, '星期一' AS WeekDayName UNION ALL
       SELECT 2, '星期二' UNION ALL
       SELECT 3, '星期三' UNION ALL
       SELECT 4, '星期四' UNION ALL
       SELECT 5, '星期五' UNION ALL
       SELECT 6, '星期六' UNION ALL
       SELECT 7, '星期日'
   )
   

2. 生成完整小时列表
   生成0到23的所有小时数：

   , Hours AS (
       SELECT 0 AS HourNum UNION ALL
       SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
       SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
       SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
       SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL
       SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL
       SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL
       SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL
       SELECT 22 UNION ALL SELECT 23
   )
   

3. 生成全时段组合表
   通过CROSS JOIN将星期和小时做笛卡尔积，得到7×24=168条完整的时段组合：

   , AllTimeSlots AS (
       SELECT w.WeekDayName, h.HourNum, w.WeekDayNum
       FROM WeekDays w
       CROSS JOIN Hours h
   )
   

4. 左连接实际统计数据
   将全时段表与你的收据统计逻辑左连接，用COALESCE把空值转为0：

   , ReceiptStats AS (
       -- 替换为你原有的统计逻辑，可添加时间范围过滤
       SELECT 
           DATENAME(WEEKDAY, ReceiptTime) AS WeekDayName,
           DATEPART(HOUR, ReceiptTime) AS HourNum,
           COUNT(*) AS ReceiptCount
       FROM Receipts
       -- WHERE ReceiptTime BETWEEN '起始日期' AND '结束日期'
       GROUP BY DATENAME(WEEKDAY, ReceiptTime), DATEPART(HOUR, ReceiptTime)
   )
   SELECT 
       ats.WeekDayName,
       ats.HourNum,
       COALESCE(rs.ReceiptCount, 0) AS ReceiptCount
   FROM AllTimeSlots ats
   LEFT JOIN ReceiptStats rs 
       ON ats.WeekDayName = rs.WeekDayName 
       AND ats.HourNum = rs.HourNum
   ORDER BY ats.WeekDayNum, ats.HourNum;
   

注意事项 #

• 不同数据库的日期函数有差异：
  • MySQL 用DAYNAME(ReceiptTime)代替DATENAME(WEEKDAY, ...)，HOUR(ReceiptTime)代替DATEPART(HOUR, ...)
  • PostgreSQL 用TO_CHAR(ReceiptTime, 'Day')获取星期名，EXTRACT(HOUR FROM ReceiptTime)获取小时数
• 加入WeekDayNum是为了保证最终结果按周一到周日的顺序排列，避免字符串排序导致的顺序错乱

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