MS Access中基于双日期列统计操作数的查询问题

问题说明 #

要在MS Access中编写查询，基于表的start_date和end_date两个日期字段，统计指定某月所有日期的当日操作数量：

• 统计每个日期的start_date出现次数（start_count）
• 统计每个日期的end_date出现次数（end_count）
• 必须展示那些start_count或end_count为0的日期
• 限制不能使用UNION语句，原采用FULL OUTER JOIN的写法报错

表结构包含字段：start_date、end_date、f1、f2、f3，存在跨日期的记录。

原查询的问题 #

MS Access本身不支持FULL OUTER JOIN语法，且原查询逻辑仅能统计有记录的日期，无法覆盖目标月份的所有日期，满足不了显示count为0的需求。

正确解决方案 #

核心思路是先生成目标月份的所有连续日期，再分别关联start和end的统计结果，用左连接保证所有日期都被保留，空值补0。

完整SQL代码

-- 替换这里的目标年月：比如2024年5月，就写#2024-05-01#和#2024-05-31#
SELECT 
    DateList.Date AS 统计日期,
    Nz(StartStats.start_count, 0) AS start_count,
    Nz(EndStats.end_count, 0) AS end_count
FROM 
    -- 生成目标月份的所有日期列表
    (SELECT 
        DateAdd("d", [Number]-1, #2024-05-01#) AS Date
     FROM 
        (SELECT TOP 31 Number 
         FROM MsysObjects 
         WHERE Type=1) AS Numbers
     WHERE 
        DateAdd("d", [Number]-1, #2024-05-01#) <= #2024-05-31#) AS DateList
LEFT JOIN 
    -- 统计每日start_date的数量
    (SELECT start_date, COUNT(*) AS start_count 
     FROM tbl 
     WHERE start_date BETWEEN #2024-05-01# AND #2024-05-31#
     GROUP BY start_date) AS StartStats
ON DateList.Date = StartStats.start_date
LEFT JOIN 
    -- 统计每日end_date的数量
    (SELECT end_date, COUNT(*) AS end_count 
     FROM tbl 
     WHERE end_date BETWEEN #2024-05-01# AND #2024-05-31#
     GROUP BY end_date) AS EndStats
ON DateList.Date = EndStats.end_date
ORDER BY DateList.Date;

代码说明

1. 日期列表生成：利用Access系统表MsysObjects生成连续数字，再通过DateAdd转换为目标月份的所有日期，注意根据月份天数调整TOP 31（比如2月可改成29或28），同时用WHERE限制在目标月份内。
2. 统计子查询：分别对start_date和end_date按日期分组计数，同时过滤目标月份的数据提升效率。
3. 左连接与空值处理：用LEFT JOIN保留所有日期，再用Nz()函数把空的count值替换为0，满足显示0的需求。
4. 参数替换：需要手动替换SQL里的目标年月的起始和结束日期，比如要统计2024年6月，就把#2024-05-01#改成#2024-06-01#，#2024-05-31#改成#2024-06-30#。

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