在SQL Server中为已取消预约添加后续预约时间字段的实现方案

原查询语句 #

SELECT 
     APPT_ID  
    ,PATIENT_ID  
    ,APPT_DATETIME  
    ,APPT_STATUS  
FROM 
    APPT_TABLE  
WHERE 
    APPT_STATUS = 'Cancelled'

原查询结果 #

APPT_ID     PATIENT_ID  APPT_DATETIME       APPT_STATUS  
-------------------------------------------------------
1           12345       2022-07-01 10:30    Cancelled  
2           56789       2022-07-02 11:45    Cancelled  
3           11223       2022-07-02 11:45    Cancelled   
4           11224       2022-07-02 11:45    Cancelled   

需求说明 #

基于APPT_TABLE表，为每条已取消的预约记录新增NEXT_APPT_DATETIME字段，该字段表示同一患者在当前取消预约时间之后的下一个预约时间；若患者无后续预约，则显示NULL。预期输出如下：

预期输出结果 #

APPT_ID     PATIENT_ID  APPT_DATETIME       APPT_STATUS NEXT_APPT_DATETIME  
----------------------------------------------------------------------------
1           12345       2022-07-01 10:30    Cancelled   2022-07-15 09:00  
2           56789       2022-07-02 11:45    Cancelled   2022-07-05 11:00  
3           11223       2022-07-02 11:45    Cancelled   NULL        (i.e. no next appt)  
4           11224       2022-07-02 11:45    Cancelled   2022-07-02 11:45

最优实现方案 #

在SQL Server中，**窗口函数LEAD()**是最高效的解决方案——它无需自关联或嵌套子查询，直接按患者分组获取后续预约时间，代码简洁且性能优异。

核心实现代码 #

SELECT 
     APPT_ID  
    ,PATIENT_ID  
    ,APPT_DATETIME  
    ,APPT_STATUS  
    -- 按患者分组、预约时间排序，获取下一条预约的时间
    ,LEAD(APPT_DATETIME) OVER (
        PARTITION BY PATIENT_ID 
        ORDER BY APPT_DATETIME ASC
    ) AS NEXT_APPT_DATETIME
FROM 
    APPT_TABLE  
WHERE 
    APPT_STATUS = 'Cancelled'

代码说明 #

1. PARTITION BY PATIENT_ID：将数据按患者ID分组，确保只查询同一患者的后续预约
2. ORDER BY APPT_DATETIME ASC：按预约时间升序排列，保证获取的是当前取消预约之后的最早下一个预约
3. LEAD()函数：默认取分组内当前行的下一行数据，若当前行是分组内最后一条，则返回NULL，完美匹配无后续预约的场景

如果需要严格筛选时间晚于当前取消预约的记录（比如要排除同一时间的预约），可以使用OUTER APPLY结合子查询实现：

精准筛选备选方案 #

SELECT 
     a.APPT_ID  
    ,a.PATIENT_ID  
    ,a.APPT_DATETIME  
    ,a.APPT_STATUS  
    ,b.NEXT_APPT_DATETIME
FROM 
    APPT_TABLE a
OUTER APPLY (
    SELECT TOP 1 APPT_DATETIME AS NEXT_APPT_DATETIME
    FROM APPT_TABLE 
    WHERE PATIENT_ID = a.PATIENT_ID 
      AND APPT_DATETIME > a.APPT_DATETIME
    ORDER BY APPT_DATETIME ASC
) b
WHERE 
    a.APPT_STATUS = 'Cancelled'

方案对比 #

• LEAD()窗口函数：性能最优，执行效率高，代码简洁，适合绝大多数场景
• OUTER APPLY方案：能精准控制时间筛选逻辑，但性能略低于窗口函数，仅在特殊需求下使用

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