欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-06-11 11:06:46
下面我们来看下,如何利用它来删除一个表中重复记录: 复制代码 代码如下: if exists(select * from tempdb.information_schema...
下面我们来看下,如何利用它来删除一个表中重复记录:
复制代码 代码如下:

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