(1)sql查询重复记录并统计重复次数
select userName,count(*)重复次数 from user group by userName having count(*)>1
(2)查询重复记录明细
select * from user
where userName in (select userName from user group by userName having count(userName) > 1)
(3) 只保留一条数据
--删除重复数据,即从重复的数据中,删除掉需要保留数据之外的所有信息:
DELETE
FROM
dept
WHERE
dname IN ( SELECT * FROM ( SELECT dname FROM dept GROUP BY dname HAVING count( dname ) > 1 ) a )
AND deptno NOT IN ( SELECT * FROM ( SELECT min( deptno ) AS deptno FROM dept GROUP BY dname HAVING count( dname ) > 1 ) b )