Sql: 查询重复数据和删除重复数据

1. 单列

select * from test
where name in (select name from test group by name having count
(name) > 1

  select * from [部门信息汇总]
where 有效否 = 1 and [部门名称] in (select [部门名称] from [部门信息汇总] where 有效否=1 group by [部门名称] having count
([部门名称]) > 1) 
and 所在 in (select 所在 from[部门信息汇总] group by 所在 having count
(所在) > 1)

2 多列

SELECT a.* FROM test a,(
SELECT name,code
FROM test 
GROUP BY name,code
HAVING COUNT(1)>1

) AS b
WHERE a.name=b.name AND a.code=b.code

3 删除重复数据

以删除重复身份证为例

delete from Test
where 身份证号 in( select 身份证号 from Test group by  身份证号 having count(身份证号) > 1) and 
TK not in(select  max(TK) from Test group by  身份证号 having count(身份证号) > 1 )

查询重复身份证

select 身份证号,count(*) from Test group by 身份证号 having count(*) > 1