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

注意倒数第二句脚本,我们在一个查询实现这个功能.
你可以自己执行t-sql script 看效果.希望对您开发有帮助!

作者：petter liu