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

最新推荐文章于 2024-10-11 14:59:27 发布

最新推荐文章于 2024-10-11 14:59:27 发布 · 158 阅读

本文介绍了一种使用SQL进行数据操作的方法，包括删除重复记录、插入数据及更新数值等实用技巧。通过具体示例展示了如何利用Common Table Expressions (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.youkuaiyun.com/u/20080626/00/43d0d10c-28f1-418d-a05b-663880da278a.html