如何用SQL将日期范围的缺勤统计关联到日历日期表？

不用游标循环也能高效解决这个问题，核心思路是用日历表生成需要统计的日期范围，再关联缺勤表做区间匹配，最后聚合统计人数。下面分两种情况给出具体实现：

情况1：已有现成的日历表（比如名为Calendar，包含CalendarDate字段） #

假设你的日历表已经覆盖了需要统计的日期区间（这里是2018-03-18到2018-03-20），可以直接用下面的查询：

SELECT
    c.CalendarDate AS Date,
    COUNT(DISTINCT a.Name) AS Absent
FROM
    Calendar c
LEFT JOIN
    tblAbsence a ON 
        -- 判断日历日期是否在员工缺勤的时间区间内
        c.CalendarDate >= CAST(a.Start AS DATE)
        AND c.CalendarDate <= CAST(a.End AS DATE)
WHERE
    c.CalendarDate BETWEEN '2018-03-18' AND '2018-03-20'
GROUP BY
    c.CalendarDate
ORDER BY
    c.CalendarDate;

关键逻辑说明： #

• CAST(a.Start AS DATE)和CAST(a.End AS DATE)：把带时间的起止字段转换为纯日期，方便和日历表的日期做匹配
• LEFT JOIN保证即使某天没人缺勤（虽然你的预期结果里没有这种情况），也会显示该日期和0缺勤数
• COUNT(DISTINCT a.Name)：避免同一员工在同一天被重复统计（比如如果员工有多条缺勤记录覆盖同一天）

情况2：没有现成日历表，临时生成日期范围 #

如果没有专门的日历表，可以用CTE（公共表表达式）临时生成需要的日期序列：

WITH DateRange AS (
    -- 起始日期
    SELECT CAST('2018-03-18' AS DATE) AS DateVal
    UNION ALL
    -- 递归生成后续日期，直到结束日期
    SELECT DATEADD(day, 1, DateVal)
    FROM DateRange
    WHERE DateVal < CAST('2018-03-20' AS DATE)
)
SELECT
    dr.DateVal AS Date,
    COUNT(DISTINCT a.Name) AS Absent
FROM
    DateRange dr
LEFT JOIN
    tblAbsence a ON
        dr.DateVal >= CAST(a.Start AS DATE)
        AND dr.DateVal <= CAST(a.End AS DATE)
GROUP BY
    dr.DateVal
ORDER BY
    dr.DateVal;

为什么这个方案比游标循环更好？ #

• 数据库天生擅长集合式操作，这种纯SQL查询不需要逐行遍历更新临时表，执行效率更高，尤其是数据量较大时
• 代码更简洁、易维护，不需要编写游标逻辑，减少出错概率
• 可以直接生成最终结果，不需要中间临时表（如果不需要保存结果，甚至可以不用创建新表，直接查询即可；如果需要保存，加INTO NewTable即可）

如果需要把结果保存为新表，只需要在SELECT前加INTO tblAbsentDailyStats（以SQL Server为例，不同数据库语法略有差异，比如MySQL用CREATE TABLE ... AS SELECT）。

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