笔记201 找出重复 TSQL 去掉重复的行
1 --找出重复 TSQL 去掉重复的行
2 declare @A表 table
3 ([intType] int,[name] varchar(4),[int3] int)
4 insert @A表
5 select 0,'张三',0 union all
6 select 99,'张三',0 union all
7 select 0,'李四',0 union all
8 select 99,'李四',0 union all
9 select 99,'王五',0 union all
10 select 99,'赵六',0 union all
11 select 0,'赵六',0 union all
12 select 99,'X',0 union all
13 select 0,'Y',0
14
15 --更新前
16 select * from @A表
17
18
19 --更新
20 update @A表 set int3=1 from @A表 a
21 INNER join
22 (
23 select name from @A表
24 group by name having(count(1)>1)
25 ) b on a.name=b.name
26 where intType=0
27
28 --更新后
29 select * from @A表
30
31 --扩展一下，显示name列哪些重复，重复的个数
32 SELECT name ,COUNT(*) FROM @A表 GROUP BY name HAVING COUNT(1)> 1
