在Microsoft SQL Server，如何保持不同的行只有一个特定的值?

Question

我有一个事件表。我想消除出现多次的某些值，例如

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必须只出现一次，并且只能出现3月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

一个简单的组by将消除重复的emd

下面是我对示例SQL的尝试:

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

Answer 1

这将处理EMI可以被发现2次或以上的情况:

select t.*
from #Movements t
LEFT JOIN (
SELECT EventCode, MIN(CreateDate) as CreateDate
FROM #Movements
WHERE EventCode = 'EMI'
GROUP BY EventCode
) s on s.EventCode = t.EventCode and t.CreateDate > s.CreateDate
where s.CreateDate is null

演示

Answer 2

可以是

SELECT distinct case EventCode    
when 'EMI' then min(CreateDate) over(partition by EventCode) 
else CreateDate end CreateDate, EventCode 
FROM #Movements

Answer 3

您只需要按两列分组，但对EventCode = 'EMI'所在行的CreateDate为空。然后将这些行改为MIN(CreateDate)。

SELECT
ISNULL(CASE WHEN EventCode <> 'EMI' THEN CreateDate END, MIN(CreateDate)) AS CreateDate,
EventCode
FROM #Movements
GROUP BY
EventCode,
CASE WHEN EventCode <> 'EMI' THEN CreateDate END;

,db&lt的在小提琴