添加LEFT JOIN后CARE_ID重复，如何保留该JOIN并实现去重？

我之前用OUTER APPLY获取最新的CARE_ID和诊断日期，再用聚合函数统计总数，代码运行正常。但添加这条LEFT JOIN语句后：

LEFT JOIN dbo.tblDEFINITIVE_TREATMENT Tr ON Ref.CARE_ID=Tr.CARE_ID

输出结果里出现了重复的CARE_ID。我试过用别名但没解决，请问怎么在保留这个JOIN的前提下去除重复的CARE_ID？现有相关代码如下：

LEFT JOIN dbo.ltblDIAGNOSIS diag1 ON ref.N4_2_DIAGNOSIS_CODE=diag1.DIAG_CODE
LEFT JOIN tblDEMOGRAPHICS dem ON ref.PATIENT_ID=dem.PATIENT_ID
LEFT JOIN dbo.tblDEFINITIVE_TREATMENT Tr ON Ref.CARE_ID=Tr.CARE_ID
LEFT JOIN dbo.ltblDEFINITIVE_TYPE Def ON Tr.TREATMENT=Def.TREAT_CODE

OUTER APPLY (SELECT TOP 1  N4_1_DIAGNOSIS_DATE, diag1.CARE_ID
             FROM DIAGNOSIS diag1
             WHERE ref.CARE_ID=diag1.CARE_ID
             ORDER BY N4_1_DIAGNOSIS_DATE  DESC) diag

出现重复的核心原因是tblDEFINITIVE_TREATMENT中一个CARE_ID对应多条治疗记录，LEFT JOIN后会把每条治疗记录都关联出来，导致主表记录被复制。以下是几种保留JOIN同时去重的方法：

方案1：先对治疗表去重再关联 #

如果只需要每个CARE_ID对应的一条治疗记录（比如最新的），可以先给治疗表做分组筛选，再和主表关联：

LEFT JOIN (
    SELECT CARE_ID, TREATMENT,
           -- 按治疗日期排序，取每个CARE_ID的最新治疗记录
           ROW_NUMBER() OVER (PARTITION BY CARE_ID ORDER BY TREAT_DATE DESC) AS rn
    FROM dbo.tblDEFINITIVE_TREATMENT
) Tr ON Ref.CARE_ID=Tr.CARE_ID AND Tr.rn=1
LEFT JOIN dbo.ltblDEFINITIVE_TYPE Def ON Tr.TREATMENT=Def.TREAT_CODE

如果不需要特定逻辑，仅需去重，也可以用DISTINCT：

LEFT JOIN (
    SELECT DISTINCT CARE_ID, TREATMENT -- 只保留需要关联的字段
    FROM dbo.tblDEFINITIVE_TREATMENT
) Tr ON Ref.CARE_ID=Tr.CARE_ID
LEFT JOIN dbo.ltblDEFINITIVE_TYPE Def ON Tr.TREATMENT=Def.TREAT_CODE

方案2：聚合统计时用去重计数 #

如果最终目的是统计总数，直接在聚合函数里用COUNT(DISTINCT)：

SELECT COUNT(DISTINCT Ref.CARE_ID) AS total_count
-- 其他需要查询的字段
FROM 你的主表 ref
LEFT JOIN dbo.ltblDIAGNOSIS diag1 ON ref.N4_2_DIAGNOSIS_CODE=diag1.DIAG_CODE
LEFT JOIN tblDEMOGRAPHICS dem ON ref.PATIENT_ID=dem.PATIENT_ID
LEFT JOIN dbo.tblDEFINITIVE_TREATMENT Tr ON Ref.CARE_ID=Tr.CARE_ID
LEFT JOIN dbo.ltblDEFINITIVE_TYPE Def ON Tr.TREATMENT=Def.TREAT_CODE
OUTER APPLY (SELECT TOP 1  N4_1_DIAGNOSIS_DATE, diag1.CARE_ID
             FROM DIAGNOSIS diag1
             WHERE ref.CARE_ID=diag1.CARE_ID
             ORDER BY N4_1_DIAGNOSIS_DATE  DESC) diag
-- 其他WHERE或GROUP BY条件

这样即使有重复记录，也只会统计唯一的CARE_ID数量。

方案3：用OUTER APPLY替代LEFT JOIN取治疗信息 #

参考你获取最新诊断日期的逻辑，用OUTER APPLY取每个CARE_ID对应的一条治疗记录：

OUTER APPLY (
    SELECT TOP 1 Tr.TREATMENT, Def.TREAT_NAME -- 按需选择需要的治疗字段
    FROM dbo.tblDEFINITIVE_TREATMENT Tr
    LEFT JOIN dbo.ltblDEFINITIVE_TYPE Def ON Tr.TREATMENT=Def.TREAT_CODE
    WHERE Ref.CARE_ID=Tr.CARE_ID
    ORDER BY Tr.TREAT_DATE DESC -- 按需求排序取TOP 1
) Tr_Def

这样每个CARE_ID只会关联一条治疗记录，不会产生重复。

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