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用來過濾.