在SqlServer2005中,提供了下面我们来看下,如何利用它来删除一个表中重复记录: 一个row_number()的函数,我们经常用它做DataBase数据分页。
下面我们来看下,如何利用它来删除一个表中重复记录:
- 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 看效果.希望对您开发有帮助!
