mysql 過(guò)程查詢，mysql查詢表里的重復(fù)數(shù)據(jù)方法？

MySQL里查詢表里的重復(fù)數(shù)據(jù)記錄：

先查看重復(fù)的原始數(shù)據(jù)：

場(chǎng)景一：列出username字段有重讀的數(shù)據(jù)

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;

這種方法只是統(tǒng)計(jì)了該字段重復(fù)對(duì)應(yīng)的具體的個(gè)數(shù)

場(chǎng)景二：列出username字段重復(fù)記錄的具體指：

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) 但是這條語(yǔ)句在mysql中效率太差，感覺mysql并沒有為子查詢生成臨時(shí)表。在數(shù)據(jù)量大的時(shí)候，耗時(shí)很長(zhǎng)時(shí)間

解決方法：

場(chǎng)景三：查看兩個(gè)字段都重復(fù)的記錄：比如username和passwd兩個(gè)字段都有重復(fù)的記錄：

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)

場(chǎng)景四：查詢表中多個(gè)字段同時(shí)重復(fù)的記錄：

1

select username,passwd,count(*) from hk_test group by username,passwd having count(*) > 1

參數(shù)說(shuō)明:

user_name為要查找的重復(fù)字段.

count用來(lái)判斷大于一的才是重復(fù)的.

user_table為要查找的表名.

group by用來(lái)分組

having用來(lái)過(guò)濾.