如何在SQL Server中获取每个会员分类下的最小DiagnosisID

嘿，我来帮你搞定这个需求！你想要的是每个会员在其所属的诊断分类中，找到DiagnosisID最小的那条记录，当前的查询只是把关联表的数据拉出来，没做分组筛选最小ID的操作，所以才会得到所有关联记录。

问题分析 #

你当前的查询返回了会员所有关联的诊断和分类，但没有对「会员+分类」的组合进行分组，也没筛选出每组里最小的DiagnosisID。用GROUP BY的时候如果直接选非聚合列，SQL Server会因为不符合聚合规则报错，所以需要用更合适的方法。

解决方案1：使用窗口函数（推荐） #

SQL Server支持窗口函数ROW_NUMBER()，这是最直观的实现方式：

WITH RankedDiagnoses AS (
    SELECT 
        MD.MemberID AS MID,
        MD.DiagnosisID AS DID,
        DM.DiagnosisCategoryID AS CID,
        -- 按会员ID和分类ID分组，给DiagnosisID从小到大排名
        ROW_NUMBER() OVER (PARTITION BY MD.MemberID, DM.DiagnosisCategoryID ORDER BY MD.DiagnosisID ASC) AS RowNum
    FROM MemberDiagnosis MD
    INNER JOIN DiagnosisCategoryMap DM ON MD.DiagnosisID = DM.DiagnosisID
)
SELECT MID, DID, CID
FROM RankedDiagnoses
WHERE RowNum = 1;

代码说明： #

1. 用CTE（公共表表达式）RankedDiagnoses给每条记录排名：
   • PARTITION BY MD.MemberID, DM.DiagnosisCategoryID：把数据按「会员ID+分类ID」分成不同的组
   • ORDER BY MD.DiagnosisID ASC：在每个组内，按DiagnosisID从小到大排序，排名为1的就是该组最小的ID
2. 最后筛选出RowNum = 1的记录，就是你要的目标结果。

解决方案2：使用GROUP BY+子查询 #

如果你习惯用分组的方式，也可以先计算每个「会员+分类」的最小DiagnosisID，再关联回原表获取完整记录：

SELECT 
    MD.MemberID AS MID,
    MD.DiagnosisID AS DID,
    DM.DiagnosisCategoryID AS CID
FROM MemberDiagnosis MD
INNER JOIN DiagnosisCategoryMap DM ON MD.DiagnosisID = DM.DiagnosisID
INNER JOIN (
    -- 先算出每个会员+分类的最小DiagnosisID
    SELECT 
        MemberID,
        DiagnosisCategoryID,
        MIN(DiagnosisID) AS MinDID
    FROM MemberDiagnosis MD
    INNER JOIN DiagnosisCategoryMap DM ON MD.DiagnosisID = DM.DiagnosisID
    GROUP BY MemberID, DiagnosisCategoryID
) MinDIDs 
    ON MD.MemberID = MinDIDs.MemberID 
    AND DM.DiagnosisCategoryID = MinDIDs.DiagnosisCategoryID
    AND MD.DiagnosisID = MinDIDs.MinDID;

这两种方法都能得到你想要的结果：

MID DID CID
1 2 2
3 3 3

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