如何在MS Access中按小时分组统计并补全无数据小时的0值？

原查询仅返回有工单提交的时段，要补全无工单的小时段并显示总数为0，核心思路是先构建包含统计范围内所有日期+24小时完整时段的基础数据集，再左连接原统计结果。

完整SQL方案 #

SELECT 
    dates.stat_date AS [date],
    hours.hour_num AS [hour],
    NZ(tickets.total_tickets, 0) AS [total_tickets]
FROM 
    (
        -- 生成0-23小时的完整时段列表
        SELECT 0 AS hour_num 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
    ) AS hours,
    (
        -- 生成2021年度内所有有工单的日期（可根据需求调整时间范围）
        SELECT DISTINCT DateValue(submitted) AS stat_date
        FROM table
        WHERE submitted >= #2021-01-01# AND submitted < #2022-01-01#
        -- 若需覆盖表中所有存在工单的日期，删除上述WHERE条件即可
    ) AS dates
LEFT JOIN 
    (
        -- 按日期和小时统计工单数量的子查询
        SELECT 
            DateValue(submitted) AS stat_date,
            DatePart("h", submitted) AS hour_num,
            COUNT(id) AS total_tickets
        FROM table
        WHERE submitted >= #2021-01-01# AND submitted < #2022-01-01#
        GROUP BY DateValue(submitted), DatePart("h", submitted)
    ) AS tickets ON dates.stat_date = tickets.stat_date AND hours.hour_num = tickets.hour_num
ORDER BY dates.stat_date, hours.hour_num;

关键说明 #

• 交叉连接小时表与日期表：通过UNION ALL生成0-23小时的完整列表，再和统计日期做笛卡尔积，确保每个日期的24小时都被包含。
• 左连接统计结果：左连接保留所有基础时段，无工单的时段会返回NULL，用NZ()函数将其转换为0。
• 时间范围一致性：子查询中的WHERE条件需保持一致，避免统计范围出现偏差。

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