SQL Row_Number() 删除重复数据(单字段OR多字段)-白红宇

SQL Row_Number() 删除重复数据(单字段OR多字段)

阅读量：6485 次

发布时间：2019-06-23

本文共 2669 字，大约阅读时间需要 8 分钟。

--------------------------删除单字段重复-----------------------------------------------If Exists(Select * From tempdb.Information_Schema.Tables Where Table_Name Like '#Temp%') Drop Table #temp Create Table #temp ([Id] int, [Name] varchar(50), [Age] int, [Sex] bit default 1) Go Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(1,'James',254,default) Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(1,'James',274,default) Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(1,'Jamessdf',22,default) Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(2,'Lisa',245,0) Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(2,'Lishja',24,0) Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(2,'Lisghja',225,0) Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(3,'Mirjsa',23,0) Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(3,'Mirhjsa',278,0) Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(3,'Mirghsa',23,0) Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(4,'Joghjhn',26,default) Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(5,'Abghjraham',28,default) Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(6,'Linghjcoln',30,default) Select * From #temp --删除ID重复Delete T From (Select Row_Number() Over(Partition By [ID] order By [ID]) As RowNumber,* From #Temp)T Where T.RowNumber > 1 --------------------------删除多字段重复-----------------------------------------------TRUNCATE TABLE #temp --清空表的数据Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(1,'James',254,default) Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(1,'James',274,default) Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(1,'Jamessdf',22,default) Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(2,'Lisa',245,0) Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(2,'Lisa',24,0) Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(2,'Lisghja',225,0) Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(3,'Mirjsa',23,0) Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(3,'Mirjsa',278,0) Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(3,'Mirghsa',23,0) Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(4,'Joghjhn',26,default) Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(5,'Abghjraham',28,default) Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(6,'Linghjcoln',30,default) --删除编号与名字 重复的数据  Partition By [ID],[Name]Delete T From (Select Row_Number() Over(Partition By [ID],[Name] order By [ID]) As RowNumber,* From #Temp)T Where T.RowNumber > 1

转载于:https://www.cnblogs.com/suqifeng/archive/2013/03/19/2969412.html

你可能感兴趣的文章