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

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

--找出重复 TSQL 去掉重复的行
declare @A表 table
([intType] int,[name] varchar(4),[int3] int)
insert @A表
select 0,'张三',0 union all
select 99,'张三',0 union all
select 0,'李四',0 union all
select 99,'李四',0 union all
select 99,'王五',0 union all
select 99,'赵六',0 union all
select 0,'赵六',0 union all
select 99,'X',0 union all
select 0,'Y',0

--更新前
select * from @A表


--更新
update @A表 set int3=1 from @A表 a
INNER  join
(
     select name from @A表
     group by name having(count(1)>1)
) b on a.name=b.name
where   intType=0

--更新后
select * from @A表

--扩展一下，显示name列哪些重复，重复的个数
SELECT   name ,COUNT(*) FROM @A表 GROUP BY name HAVING  COUNT(1)> 1