SQL Server中单列日期列表转多日期范围的实现问题

问题背景 #

你需要将同一Module和Employeeid下的连续日期合并为日期范围，不连续的日期则单独成范围。原本的查询逻辑在未添加日期过滤时可以正常工作，但添加起止日期过滤后，结果出现异常。

问题原因 #

你的原始查询犯了一个关键错误：先对日期进行过滤，再计算用于分组的grp字段。这样做会破坏连续日期的分组逻辑——当你过滤掉部分日期后，剩余数据的Row_number()是基于过滤后的子集计算的，而非完整的连续日期序列，导致grp无法正确标识原本的连续日期组。

举个例子：假设原本有连续日期2020-01-07、2020-01-08、2020-01-09，如果过滤掉2020-01-08，剩余日期的Row_number()会重新从1开始计算，grp的值就不再一致，原本属于同一连续组的日期会被错误拆分。

解决方案 #

正确的逻辑应该是：先计算出所有数据的连续日期分组标识，再对分组后的结果进行日期过滤。同时还要注意，连续日期的分组应该同时基于Module和Employeeid（你的原始CTE只按Module分区，这也是潜在问题）。

修改后的SQL查询如下：

WITH mycte AS (
    SELECT 
        *,
        -- 按Module和Employeeid分区，计算连续日期分组标识
        DATEADD(day, -ROW_NUMBER() OVER (PARTITION BY [Module], [Employeeid] ORDER BY [Date]), [Date]) AS grp
    FROM [to_shiftschedule]
    WHERE Employeeid = 535 -- 先过滤Employeeid，不影响连续分组逻辑
)
SELECT 
    [Module],
    [Employeeid],
    MIN([Date]) AS [Start Date],
    MAX([Date]) AS [End Date]
FROM mycte
-- 直接用日期类型比较，避免字符串转换的效率和格式问题
WHERE [Date] >= '2020-01-09' 
  AND [Date] <= '2020-01-23'
GROUP BY [Employeeid], [Module], grp
ORDER BY [Start Date] DESC;

关键优化点 #

1. 调整执行顺序：先计算连续分组标识grp，再进行日期过滤，保证分组逻辑基于完整的连续日期序列。
2. 完善分区字段：PARTITION BY同时包含Module和Employeeid，确保分组是针对同一员工同一模块的日期。
3. 优化日期比较：直接用日期类型进行比较，避免将日期转换为字符串（转换字符串不仅效率低，还可能因格式问题导致错误）。

测试验证 #

用你提供的示例数据测试（假设Employeeid为9），修改后的查询会正确输出：

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