在Microsoft SQL Server中如何仅对特定值保留唯一行

原始数据如下：

CreateDate  EventCode
------------------------
2023-03-20  EMA
2023-03-21  EMD
2023-03-22  EMD
2023-03-22  EMF
2023-03-23  EMI
2023-03-24  EMI

具体要求 #

仅保留最早的一条EMI记录（即2023-03-23的那条），删除其余重复的EMI行；而EMD这类其他事件的重复行需要全部保留。预期结果：

CreateDate  EventCode
------------------------
2023-03-20  EMA
2023-03-21  EMD
2023-03-22  EMD
2023-03-22  EMF
2023-03-23  EMI

尝试过的代码（存在问题） #

直接用GROUP BY会同时清除EMD的重复行，不符合需求：

DROP TABLE IF EXISTS #Movements

CREATE TABLE #Movements 
(
    CreateDate datetime2,
    EventCode nvarchar(3)
)

INSERT INTO #Movements (CreateDate, EventCode) VALUES ('2023-03-20', 'EMA')
INSERT INTO #Movements (CreateDate, EventCode) VALUES ('2023-03-21', 'EMD')
INSERT INTO #Movements (CreateDate, EventCode) VALUES ('2023-03-22', 'EMD')
INSERT INTO #Movements (CreateDate, EventCode) VALUES ('2023-03-22', 'EMF')
INSERT INTO #Movements (CreateDate, EventCode) VALUES ('2023-03-23', 'EMI')
INSERT INTO #Movements (CreateDate, EventCode) VALUES ('2023-03-24', 'EMI')

SELECT * FROM #Movements

SELECT EventCode 
FROM #Movements
GROUP BY EventCode

解决方案 #

使用窗口函数ROW_NUMBER()针对目标事件（EMI）进行排序编号，再筛选出符合要求的行：

WITH RankedMovements AS (
    SELECT 
        CreateDate,
        EventCode,
        ROW_NUMBER() OVER (PARTITION BY EventCode ORDER BY CreateDate) AS rn
    FROM #Movements
)
SELECT CreateDate, EventCode
FROM RankedMovements
WHERE 
    EventCode != 'EMI' 
    OR (EventCode = 'EMI' AND rn = 1);

逻辑说明 #

• 对每个EventCode按CreateDate升序编号，EMI会被标记为1、2...
• 筛选时，非EMI事件全部保留，EMI只保留编号为1的最早那条，完全匹配需求。

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