欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

删除重复Row记录数据

程序员文章站 2023-04-06 16:34:23
使用CTE,ROW_NUMBER,PARTITION BY来处理数据表重复记录。 先准备下面的数据: IF OBJECT_ID('tempdb.dbo.#Part') IS NOT NULL DROP TABLE #Part CREATE TABLE #Part ( [ID] INT, [Item] ......

使用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,可见得复记录删除,相同的记录仅留下一笔: