SqlServer2005中使用row_number()在一个查询中删除重复记录的方法

215 阅读 0 评论 142 点赞

我是靠谱客的博主阳光短靴，最近开发中收集的这篇文章主要介绍SqlServer2005中使用row_number()在一个查询中删除重复记录的方法，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

下面我们来看下,如何利用它来删除一个表中重复记录:

复制代码代码如下:

 
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',25,default) 
Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(1,'James',25,default) 
Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(1,'James',25,default) 
Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(2,'Lisa',24,0) 
Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(2,'Lisa',24,0) 
Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(2,'Lisa',24,0) 
Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(3,'Mirsa',23,0) 
Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(3,'Mirsa',23,0) 
Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(3,'Mirsa',23,0) 
Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(4,'John',26,default) 
Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(5,'Abraham',28,default) 
Insert Into #temp ([Id] , [Name] , [Age] , [Sex] ) Values(6,'Lincoln',30,default) 
Delete T From 
(Select Row_Number() Over(Partition By [ID],[Name],[Age],[Sex] order By [ID]) As RowNumber,* From #Temp)T 
Where T.RowNumber > 1 
Select * From #temp 