用CTE處理表重複資料的更新和刪除

--> --> (Roy)產生測試數據set nocount on ;if not object_id('Tempdb..#T') is null    drop table #TGoCreate table #T([Num] int,[Name] nvarchar(1))Insert #Tselect 1,N'A' union allselect 1,N'A' union allselect 2,N'B' union allselect 2,N'B'go--Delete;with CTE as(select row=row_number()over(partition by [Num],[Name] order by (select 1)),[Num],[Name] from #T)delete CTE where row>1select * from #T/*Num         Name----------- ----1           A2           B*/--insert;with CTE as(select [Num],[Name] from #T)insert CTEselect * from CTEselect * from #T/*Num         Name----------- ----1           A1           A2           B2           B*/--update;with CTE as(select row=row_number()over(partition by [Num] order by (select 1)),[Num],[Name] from #T)update CTEset [Num]=CTE.rowselect * from #T/*Num         Name----------- ----1           A2           A1           B2           B*/
 
處理重複資料查詢和其它方法
http://topic.csdn.net/u/20080626/00/43d0d10c-28f1-418d-a05b-663880da278a.html