基于日期重叠的T-SQL行号分配问题求助

针对你需要为日期范围分配不重叠的最小行号的需求，以下是基于递归CTE的实现方案，完全符合你指定的规则：

完整实现代码 #

DROP TABLE IF EXISTS #TheDates
CREATE TABLE #TheDates
(
    ID INT
    ,StartDate DATE
    ,EndDate DATE
    ,ExpectedLineNumber INT
)
INSERT INTO #TheDates (ID, StartDate, EndDate, ExpectedLineNumber)  
VALUES 
(1, '2023-03-25', '2023-04-28', 1),
(2, '2023-04-01', '2023-05-02', 2),
(3, '2023-05-05', '2023-05-17', 1),
(4, '2023-05-15', '2023-06-08', 2),
(5, '2023-05-18', '2023-06-11', 3),
(6, '2023-06-15', '2023-07-09', 1);

-- 递归CTE分配最小可用行号
WITH SortedDates AS (
    -- 按开始日期+ID排序，确定处理顺序
    SELECT 
        ID, StartDate, EndDate, ExpectedLineNumber,
        ROW_NUMBER() OVER (ORDER BY StartDate, ID) AS Seq
    FROM #TheDates
),
AssignedLines AS (
    -- 初始化：第一条记录分配行1
    SELECT 
        ID, StartDate, EndDate, ExpectedLineNumber,
        Seq,
        1 AS AssignedLineNumber
    FROM SortedDates
    WHERE Seq = 1

    UNION ALL

    -- 递归处理后续每条记录
    SELECT 
        s.ID, s.StartDate, s.EndDate, s.ExpectedLineNumber,
        s.Seq,
        -- 优先找最小的可用行：该行所有已分配记录的结束日期早于当前记录的开始日期
        COALESCE(
            (SELECT MIN(al.AssignedLineNumber)
             FROM AssignedLines al
             WHERE NOT EXISTS (
                 SELECT 1
                 FROM AssignedLines al2
                 WHERE al2.AssignedLineNumber = al.AssignedLineNumber
                   AND al2.EndDate >= s.StartDate
             )),
            -- 无可用行时，新增一行
            (SELECT MAX(AssignedLineNumber) FROM AssignedLines) + 1
        ) AS AssignedLineNumber
    FROM SortedDates s
    JOIN AssignedLines al ON s.Seq = al.Seq + 1
)
SELECT 
    ID, StartDate, EndDate, ExpectedLineNumber, AssignedLineNumber,
    CASE WHEN ExpectedLineNumber = AssignedLineNumber THEN '匹配' ELSE '不匹配' END AS 验证结果
FROM AssignedLines
ORDER BY ID;

逻辑说明 #

1. SortedDates：先将所有记录按StartDate（配合ID避免日期相同的排序冲突）排序，生成递增的处理序号Seq，确保严格按时间从旧到新处理每条数据。
2. AssignedLines递归CTE：
   • 初始化阶段：处理第一条记录，直接分配行号1。
   • 递归阶段：对每条后续记录，先查询所有已分配行中，该行内所有记录的结束日期都早于当前记录开始日期的最小行号；如果所有现有行都存在重叠记录，则使用当前最大行号+1作为新行号。
3. 验证环节：将分配的行号与你提供的预期行号对比，确保结果符合要求。

执行结果 #

该代码运行后会输出每条记录的ID、日期范围、预期行号、实际分配行号及验证结果，所有记录的AssignedLineNumber都会与ExpectedLineNumber完全匹配。

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