删除重复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,可见得复记录删除,相同的记录仅留下一笔:
上一篇: Collection集合
下一篇: T-SQL PIVOT 行列转换