如何在SQL Server中按首次RandStat过滤重复RepID记录

我正在使用SQL Server，现有如下表格：

RepID  ICD  Diagnosed  RandStat
-------------------------------
1001   A    1          11
1002   A    0          11
1003   A    0          11
1005   A    1          11

1003   A    1          22
1005   A    0          22
1008   A    1          22
1009   A    0          22

1001   A    0          33
1004   A    1          33
1005   A    0          33
1007   A    0          33

1003   A    0          44
1004   A    0          44
1005   A    1          44
1007   A    1          44


1001   B    0          11
1004   B    1          11
1006   B    1          11
1008   B    1          11

1004   B    0          22
1006   B    1          22
1008   B    0          22
1009   B    1          22

1001   B    0          33
1002   B    1          33
1003   B    1          33
1006   B    0          33

1003   B    1          44
1004   B    0          44
1005   B    0          44
1007   B    1          44

需求说明 #

针对同一ICD，按RandStat升序排列，过滤掉重复出现的RepID记录：若某RepID已出现在RandStat 11中，则移除其在RandStat 22及以上的所有同ICD记录。

预期结果 #

RepID  ICD  Diagnosed  RandStat
-------------------------------
1001   A    1          11
1002   A    0          11
1003   A    0          11
1005   A    1          11

1008   A    1          22
1009   A    0          22

1004   A    1          33
1007   A    0          33

1001   B    0          11
1004   B    1          11
1006   B    1          11
1008   B    1          11

1009   B    1          22

1002   B    1          33
1003   B    1          33

1005   B    0          44
1007   B    1          44

尝试的查询语句（未得到预期结果） #

SELECT A.*
FROM MyTable A 
INNER JOIN MyTable B ON A.RepID = B.RepID
                     AND A.ICD = B.ICD
                     AND A.RandStat < B.RandStat

解决方案 #

核心逻辑是保留每个ICD下每个RepID的首次出现记录（按RandStat升序），过滤后续重复的RepID记录，以下两种方法均可实现：

方法一：使用窗口函数标记首次出现 #

WITH RankedRecords AS (
    SELECT 
        *,
        -- 按ICD分组、RepID分区，RandStat升序排序，标记每条记录的出现顺序
        ROW_NUMBER() OVER (PARTITION BY ICD, RepID ORDER BY RandStat) AS rn
    FROM MyTable
)
SELECT RepID, ICD, Diagnosed, RandStat
FROM RankedRecords
WHERE rn = 1
ORDER BY ICD, RandStat, RepID;

方法二：使用关联查询过滤已出现的RepID #

SELECT t.*
FROM MyTable t
WHERE NOT EXISTS (
    SELECT 1
    FROM MyTable t2
    WHERE t2.ICD = t.ICD
      AND t2.RepID = t.RepID
      AND t2.RandStat < t.RandStat
)
ORDER BY t.ICD, t.RandStat, t.RepID;

两种方法都能精准匹配你的预期结果，自动过滤掉同一ICD下RepID的后续重复记录。

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