删除重复Row记录数据

使用CTE,ROW_NUMBER,PARTITION BY来处理数据表重复记录。

先准备下面的数据：

 

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

 

处理，并执行把重复行删除。

 

;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，可见得复记录删除，相同的记录仅留下一笔：