我有一张事件表.我想删除某些多次出现的值,例如
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
简单的GROUP BY将消除重复的EMD
下面是我try 使用示例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