MYSQL查询一张表中重复的所有记录数据（数据库表重复数据删除处理）

1、一张表中有username 字段数据重复，查询出username 字段数据重复的所有数据，如下

select * from table a where (a.username) in  (select username from table group by username  having count(*) > 1) order by username desc

2、删除表中多余的重复记录，如下是删除企业信息表里企业统一信用代码（unifiedcode）重复的数据，通过province_code值不为空来判断应该删除的数据

delete from unit_company where id in (select A.id from (select id from unit_company D where D.unifiedcode in  (select unifiedcode from unit_company group by unifiedcode having count(unifiedcode) > 1) and ifnull(D.province_code, '') = '') A )

3、查找表中多个字段重复的记录（多个字段），如下是查询id和user_name字段数据重复的所有数据

select * from table a
where (a.id,a.user_name) in (select id,user_name from table group by id having count(*) > 1) order by user_name desc