SQL Server LEAD函数实践

CREATE TABLE [dbo].[Tender_Process_RoundInfo]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[ProjectID] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[RoundNumber] [int] NOT NULL,
[BeginTime] [datetime] NOT NULL,
[EndTime] [datetime] NOT NULL
) ON [PRIMARY]
GO

WITH OrderedRounds AS
    (   SELECT      ProjectID, RoundNumber, BeginTime, EndTime,
                    -- 使用 LEAD 获取下一轮的开始时间
                    LEAD(BeginTime, 1) OVER (PARTITION BY ProjectID ORDER BY RoundNumber) AS next_begintime
            FROM    dbo.Tender_Process_RoundInfo
            WHERE   ProjectID='1'), CalculatedStages AS
    (   SELECT      ProjectID, RoundNumber,
                    -- 当前轮次的持续时间（秒）
                    DATEDIFF(SECOND, BeginTime, EndTime)      AS duration_seconds,
                    -- 当前轮次结束到下一轮开始之间的休息间隔（秒）
                    DATEDIFF(SECOND, EndTime, next_begintime) AS rest_seconds
            FROM    OrderedRounds)
SELECT      stage=CONCAT('第', RoundNumber, '轮报价倒计时'), seconds=duration_seconds, sortorder=RoundNumber * 10
    FROM    CalculatedStages
UNION ALL
SELECT      stage=CONCAT('休息间隔 ', RoundNumber, '-', RoundNumber+1), seconds=rest_seconds, sortorder=RoundNumber * 10+1
    FROM    CalculatedStages
    WHERE   rest_seconds IS NOT NULL
    ORDER BY    sortorder

结果：