笔记201 找出重复 TSQL 去掉重复的行

笔记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

 

posted @ 2013-08-04 13:17  桦仔  阅读(591)  评论(0)    收藏  举报