使用CTE,ROW_NUMBER,PARTITION BY来处理数据表重复记录。
先准备下面的数据:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
IF OBJECT_ID('tempdb.dbo.#Part') IS NOT NULL DROP TABLE #Part CREATE TABLE #Part ( [ID] INT, [Item] NVARCHAR(40), [Category] NVARCHAR(25), [Qty] DECIMAL(18,2) ) GO INSERT INTO #Part ([ID],[Item],[Category],[Qty]) VALUES (23394,'I32-GG443-QT0098-0001','S',423.65), (45008,'I38-AA321-WS0098-0506','B',470.87), (14350,'K38-12321-5456UD-3493','B',200.28), (64582,'872-RTDE3-Q459PW-2323','T',452.44), (23545,'098-SSSS1-WS0098-5526','S',500.00), (80075,'B78-F1H2Y-5456UD-2530','T',115.06), (53567,'PO0-7G7G7-JJY098-0077','Q',871.33), (44349,'54F-ART43-6545NN-2514','S',934.39), (36574,'X3C-SDEWE-3ER808-8764','Q',607.88), (36574,'RVC-43ASE-H43QWW-9753','U',555.19), (14350,'K38-12321-5456UD-3493','B',200.28), (64582,'872-RTDE3-Q459PW-2323','T',452.44), (80075,'B78-F1H2Y-5456UD-2530','T',115.06), (53567,'PO0-7G7G7-JJY098-0077','Q',871.33), (44349,'54F-ART43-6545NN-2514','S',934.39), (44349,'54F-ART43-6545NN-2514','S',934.39), (36574,'X3C-SDEWE-3ER808-8764','Q',607.88) GO
处理,并执行把重复行删除。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
;WITH Duplicate_Records AS ( SELECT [ID], [Item], [Category], [Qty], ROW_NUMBER() OVER ( PARTITION BY [ID], [Item], [Category], [Qty] ORDER BY [ID] ) AS [row_num] FROM #Part ) DELETE FROM Duplicate_Records WHERE [row_num] > 1;
接下来,运行下面的SQL,可见得复记录删除,相同的记录仅留下一笔: