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