TSQL日期统计需求：根据假期起止数据统计每日休假人数

嘿，要解决按日期统计每日休假人数的需求，核心思路其实很简单：先生成一个覆盖所有需要统计的日期的列表，再把这个日期列表和你的假期数据关联起来，就能算出每天有多少人在休假啦。下面给你两种实用的TSQL实现方案：

方法一：用CTE生成日期范围（无需额外表） #

这种方法适合临时统计场景，不需要提前创建额外的表，通过递归CTE自动生成从最早的未来假期开始日到最晚的未来假期结束日之间的所有日期，再关联假期数据统计人数。

WITH DateRange AS (
    -- 先拿到所有未来假期的起止边界日期
    SELECT 
        MIN(b.data_start) AS StartDate,
        MAX(b.data_end) AS EndDate
    FROM registry a 
    INNER JOIN holidays b ON a.id = b.id_anagrafica
    WHERE b.data_start >= GETDATE()

    UNION ALL

    -- 递归生成每一天的日期
    SELECT 
        DATEADD(DAY, 1, StartDate),
        EndDate
    FROM DateRange
    WHERE StartDate < EndDate
)
SELECT 
    CONVERT(VARCHAR(10), dr.StartDate, 103) AS data, -- 转成你要的DD/MM/YYYY格式
    COUNT(b.id_anagrafica) AS num_pers_in_ferie
FROM DateRange dr
LEFT JOIN holidays b 
    ON dr.StartDate BETWEEN b.data_start AND b.data_end
    AND b.data_start >= GETDATE() -- 只统计未来的假期
GROUP BY dr.StartDate
ORDER BY dr.StartDate;

方法二：用自定义日期表（高效，适合频繁统计） #

如果需要经常做这类统计，建议提前建一个专门的日期维度表，里面存好所有可能用到的日期，这样查询速度会快很多，后续扩展其他日期相关统计也方便。

第一步：创建并填充日期表 #

-- 创建日期维度表
CREATE TABLE DateDimension (
    DateKey DATE PRIMARY KEY,
    Year INT,
    Month INT,
    Day INT
    -- 还可以加星期、季度这类属性，按需扩展
);

-- 填充2018到2030年的日期（可根据需求调整范围）
DECLARE @StartDate DATE = '2018-01-01';
DECLARE @EndDate DATE = '2030-12-31';

WHILE @StartDate <= @EndDate
BEGIN
    INSERT INTO DateDimension (DateKey, Year, Month, Day)
    VALUES (@StartDate, YEAR(@StartDate), MONTH(@StartDate), DAY(@StartDate));
    SET @StartDate = DATEADD(DAY, 1, @StartDate);
END

第二步：用日期表统计每日休假人数 #

SELECT 
    CONVERT(VARCHAR(10), dd.DateKey, 103) AS data,
    COUNT(h.id_anagrafica) AS num_pers_in_ferie
FROM DateDimension dd
LEFT JOIN holidays h 
    ON dd.DateKey BETWEEN h.data_start AND h.data_end
    AND h.data_start >= GETDATE()
-- 只筛选未来假期覆盖的日期范围
WHERE dd.DateKey >= (SELECT MIN(data_start) FROM holidays WHERE data_start >= GETDATE())
  AND dd.DateKey <= (SELECT MAX(data_end) FROM holidays WHERE data_start >= GETDATE())
GROUP BY dd.DateKey
ORDER BY dd.DateKey;

小提示 #

• 用CONVERT(VARCHAR(10), ..., 103)是为了输出你需要的DD/MM/YYYY日期格式，如果需要其他格式，可以调整第三个参数（比如120对应YYYY-MM-DD）。
• 如果只想统计从今天开始的日期，直接在日期范围的筛选条件里加上dr.StartDate >= GETDATE()（方法一）或者dd.DateKey >= GETDATE()（方法二）就行。

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