基于单字段为SQL Server数据生成起止时间及关联明细行

原始数据 #

方案1：生成每个表头的起止时间 #

利用SQL Server的LEAD()窗口函数，获取下一个表头的开始时间作为当前表头的结束时间，最后一个表头可自定义默认结束时间。

SELECT
    HeaderID,
    StartDateTime AS HeaderStartDateTime,
    LEAD(StartDateTime, 1, '2020-12-14T13:07:07.875') OVER (ORDER BY StartDateTime) AS HeaderEndDateTime
FROM
    YourTableName
WHERE
    HeaderID != 0
ORDER BY
    StartDateTime;

执行结果：

方案2：为明细行匹配对应的表头ID #

提供两种实现方式，可根据数据量选择更高效的方案：

方法1：子查询匹配 #

通过子查询找到每条明细行对应的、时间最近的前置表头行：

SELECT
    d.ID,
    d.StartDateTime,
    (SELECT TOP 1 HeaderID
     FROM YourTableName h
     WHERE h.HeaderID != 0 AND h.StartDateTime <= d.StartDateTime
     ORDER BY h.StartDateTime DESC) AS HeaderID
FROM
    YourTableName d
WHERE
    d.HeaderID = 0;

方法2：表头区间关联（大表更高效） #

先生成表头的时间区间临时表，再关联明细行匹配所属区间：

WITH HeaderRanges AS (
    SELECT
        HeaderID,
        StartDateTime AS StartRange,
        LEAD(StartDateTime, 1, '9999-12-31') OVER (ORDER BY StartDateTime) AS EndRange
    FROM YourTableName
    WHERE HeaderID != 0
)
SELECT
    d.ID,
    d.StartDateTime,
    hr.HeaderID
FROM YourTableName d
JOIN HeaderRanges hr ON d.StartDateTime >= hr.StartRange AND d.StartDateTime < hr.EndRange
WHERE d.HeaderID = 0;

执行结果：

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