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

作者：Petter Liu
出处：http://www.cnblogs.com/wintersun/
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
该文章也同时发布在我的独立博客中-Petter Liu Blog。