下面我們來看下,如何利用它來刪除一個表中重復記錄:
復制代碼 代碼如下:
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
您可能感興趣的文章:- SQLSERVER分頁查詢關于使用Top方式和row_number()解析函數的不同
- SQLServer中Partition By及row_number 函數使用詳解
- sqlServer使用ROW_NUMBER時不排序的解決方法
- sqlserver巧用row_number和partition by分組取top數據
- sqlserver2005使用row_number() over分頁的實現方法
- SqlServer 2005中使用row_number()在一個查詢中刪除重復記錄
- SQLSERVER 2005的ROW_NUMBER、RANK、DENSE_RANK的用法
- sqlserver 通用存儲過程分頁代碼(附使用ROW_NUMBER()和不使用ROW_NUMBER()兩種情況性能分析)
- SQL Server中row_number分頁查詢的用法詳解
