SQL Server 2015下如何统计每日历史开放工单数量？

核心思路是通过日期表与工单表的交叉关联，筛选出每个日历日处于开放状态的工单，再按日期分组统计数量。需要注意处理关单日期为NULL（工单未关闭）、以及开单日期晚于关单日期的异常数据。

步骤说明 #

• 先过滤工单表中的异常数据：排除开单日期晚于关单日期的无效工单
• 将日期表与处理后的工单表做交叉连接
• 筛选出满足「日历日在工单开单日期之后，且在关单日期之前（或关单日期为空）」的记录
• 按日期分组，统计每个日期的有效工单数量

示例SQL代码 #

SELECT 
    d.日期,
    COUNT(t.工单编号) AS 开放工单数量
FROM 
    日期表 d
CROSS JOIN 
    工单表 t
WHERE 
    -- 过滤开单日期晚于关单日期的异常工单
    (t.关单日期 IS NULL OR t.开单日期 <= t.关单日期)
    -- 日历日 >= 开单日期
    AND d.日期 >= t.开单日期
    -- 日历日 <= 关单日期 或者 关单日期为空（未关闭）
    AND (d.日期 <= t.关单日期 OR t.关单日期 IS NULL)
GROUP BY 
    d.日期
ORDER BY 
    d.日期;

补充说明 #

• 如果日期表数据量较大，建议通过年份字段先缩小范围（比如WHERE d.年份 = 2021），提升查询效率
• 关单日期为NULL时，视为工单当前仍处于开放状态，因此所有晚于开单日期的日历日都会统计该工单
• 交叉连接会生成日期表和工单表的所有组合，再通过条件筛选出符合要求的记录，这是统计这类区间覆盖问题的常用方法

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