视频控制器驱动,小梓药,问道地府指引
下面我们来看下,如何利用它来删除一个表中重复记录:
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 看效果.希望对您开发有帮助!
如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！
点击进行留言回复
我要评论
网友评论