用CTE处理表重复数据的更新和删除

--> --> (Roy)生成測試數據 set nocount on ; if not object_id('Tempdb..#T') is null drop table #T Go Create table #T([Num] int,[Name] nvarchar(1)) Insert #T select 1,N'A' union all select 1,N'A' union all select 2,N'B' union all select 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>1 select * from #T /* Num Name ----------- ---- 1 A 2 B */ --insert ;with CTE as (select [Num],[Name] from #T) insert CTE select * from CTE select * from #T /* Num Name ----------- ---- 1 A 1 A 2 B 2 B */ --update ;with CTE as (select row=row_number()over(partition by [Num] order by (select 1)),[Num],[Name] from #T) update CTE set [Num]=CTE.row select * from #T /* Num Name ----------- ---- 1 A 2 A 1 B 2 B */

处理重复数据查询和其它方法

http://topic.csdn.net/u/20080626/00/43d0d10c-28f1-418d-a05b-663880da278a.html