https://blog.51cto.com/13689359/2401599?source=dra
MySQL里查询表里的重复数据记录：
先查看重复的原始数据：
场景一：列出username字段有重读的数据
|
1
2
3
|
select username,count(*) as count from hk_test group by username having count>1;
SELECT username,count(username) as count FROM hk_test GROUP BY username HAVING count(username) >1 ORDER BY count DESC;
|
这种方法只是统计了该字段重复对应的具体的个数
场景二：列出username字段重复记录的具体指：
|
1
2
3
4
5
|
select * from hk_test where username in (select username from hk_test group by username having count(username) > 1)
SELECT username,passwd FROM hk_test WHERE username in ( SELECT username FROM hk_test GROUP BY username HAVING count(username)>1)
但是这条语句在mysql中效率太差，感觉mysql并没有为子查询生成临时表。在数据量大的时候，耗时很长时间
|
解决方法：
场景三：查看两个字段都重复的记录：比如username和passwd两个字段都有重复的记录：
|
1
2
|
select * from hk_test a
where (a.username,a.passwd) in (select username,passwd from hk_test group by username,passwd having count(*) > 1)
|
场景四：查询表中多个字段同时重复的记录：
|
1
|
select username,passwd,count(*) from hk_test group by username,passwd having count(*) > 1
|
参数说明:
user_name为要查找的重复字段.
count用来判断大于一的才是重复的.
user_table为要查找的表名.
group by用来分组
having用来过滤.
