SqlServer 2005中使用row_number()在一个查询中删除重复记录 |
|
本文标签：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 看效果.希望对您开发有帮助! |
