SQL Server中按特定条件合并两张表：避免ICU记录重复统计

现有两个数据集：

DF1（患者住院就诊记录表）： #

DF2（ICU就诊记录表）： #

需生成第三张表，追踪患者出院后30天内是否入住ICU。若同一患者多次住院的出院日期均在某ICU就诊日期的前30天内，该ICU记录仅需关联其中一次住院记录（任意一次均可），其余住院记录的Service_Code为NULL。

使用以下SQL语句时，同一ICU记录被关联到所有符合条件的住院记录，导致重复统计：

select distinct
                d.PT_ID,
                d.Hospital_ID,
                d.Admit_Dt,
                d.Discharge_DT,
                d.Discharge_ID,
                t.Service_Code,
from #df1 d
                left join #df2 t
                        on t.PT_ID = d.PT_ID and t.ICU_Admit_Dt between d.Discharge_DT and 
                        DATEADD(day, 30, d.Discharge_DT)
order by PT_ID

当前错误输出： #

核心思路是通过窗口函数给每个ICU记录匹配的住院记录标记唯一序号，仅保留第一条的Service_Code，其余设为NULL。以下是实现代码：

WITH matched_records AS (
    SELECT 
        d.PT_ID,
        d.Hospital_ID,
        d.Admit_Dt,
        d.Discharge_DT,
        d.Discharge_ID,
        t.Service_Code,
        -- 按患者ID和ICU入院日期分组，给匹配的住院记录分配序号（此处按出院日期倒序，优先关联最近出院的记录）
        ROW_NUMBER() OVER (PARTITION BY t.PT_ID, t.ICU_Admit_Dt ORDER BY d.Discharge_DT DESC) AS rn
    FROM #df1 d
    LEFT JOIN #df2 t
        ON t.PT_ID = d.PT_ID 
        AND t.ICU_Admit_Dt BETWEEN d.Discharge_DT AND DATEADD(day, 30, d.Discharge_DT)
)
SELECT 
    PT_ID,
    Hospital_ID,
    Admit_Dt,
    Discharge_DT,
    Discharge_ID,
    -- 仅保留序号为1的记录的Service_Code，其余设为NULL
    CASE WHEN rn = 1 THEN Service_Code ELSE NULL END AS Service_Code
FROM matched_records
ORDER BY PT_ID, Discharge_DT;

逻辑说明 #

1. 先用CTE关联两张表，筛选出符合“出院后30天内入住ICU”的记录
2. 用ROW_NUMBER()给每个患者的同一次ICU就诊记录匹配的住院记录排序（排序规则可按需调整，比如换成ORDER BY d.Admit_Dt ASC关联最早住院的记录，不影响需求的“任意一次”要求）
3. 最后通过CASE语句，只让第一条匹配的住院记录保留Service_Code，其余设为NULL

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