TSQL查询两段日期间所有日期 解决重复记录与性能问题

实现思路 #

• 先对原表的[WkStart]和[WkEnd]做去重处理，同一周区间只保留1条，从源头避免重复生成日期
• 采用数字理货表（Tally Table）生成连续日期，相比递归CTE无递归栈开销，大数据量下性能提升非常明显
• 通过EXISTS判断当前生成的日期是否在原表的[MyDate]字段中存在，直接得到正确的bit值，不需要额外去重

实现代码 #

-- 生成0~1000的数字序列，足够覆盖所有周区间的天数需求，可根据实际场景扩展上限
WITH Tally (n) AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
    FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2
),
-- 第一步：去重所有唯一的周区间，避免同一周重复生成日期
DistinctWeeks AS
(
    SELECT DISTINCT WkStart, WkEnd
    FROM @Dates
),
-- 第二步：生成每个周区间内的所有连续日期
AllWeekDates AS
(
    SELECT DATEADD(DAY, t.n, dw.WkStart) AS DateVal
    FROM DistinctWeeks dw
    JOIN Tally t ON t.n <= DATEDIFF(DAY, dw.WkStart, dw.WkEnd)
)
-- 第三步：判断日期是否为MyDate，得到最终结果
SELECT 
    awd.DateVal,
    CAST(CASE WHEN EXISTS (SELECT 1 FROM @Dates d WHERE d.MyDate = awd.DateVal) THEN 1 ELSE 0 END AS BIT) AS isMyDate
FROM AllWeekDates awd
ORDER BY awd.DateVal

提示：如果你的数据库中有预先构建的永久数字理货表，直接替换上面的Tally CTE部分，性能会进一步提升。

方案优势 #

• 解决了原方案的重复日期问题：周区间先去重，同一周只会生成一次完整的日期序列
• 性能优异：理货表采用系统表笛卡尔积生成数字序列，无需递归，即使千万级数据量也能高效运行
• 逻辑简洁：通过EXISTS判断bit值，自动处理同一周存在多个MyDate的场景，只要日期匹配任意一个MyDate就返回1，完全符合需求

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