--------------------------删除单字段重复-----------------------------------------------
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
