假設模型表是:

1. 根據單字段過濾:

SELECT user_name, COUNT(*) as count
FROM sys_user
GROUP BY user_name
HAVING count > 1;

結果:

2. 根據多個字段查詢重復數據

SELECT user_name, email, COUNT(*) as count
FROM sys_user
GROUP BY user_name, email
HAVING count > 1;

結果:

3. 對一個字段查找重復記錄

SELECT * FROM sys_user WHERE user_name IN(
SELECT user_name FROM sys_user GROUP BY user_name HAVING COUNT(user_name) > 1
);

結果:

4. 刪除重復記錄，只保留user_id字段值最大的記錄

delete from sys_user where user_id not in (select maxid from (select max(user_id) as maxid from sys_user group by user_name) b);

結果:

5. 對多個字段查找重復記錄

SELECT* 
FROM( SELECT *, CONCAT( user_name, email ) AS nameAndEmail FROM sys_user ) t 
WHEREt.nameAndEmail IN (SELECTnameAndEmail FROM( SELECT CONCAT( user_name, email ) AS nameAndEmail FROM sys_user ) tt GROUP BYnameAndEmail HAVINGcount( nameAndEmail ) > 1 )

結果:

6. 刪除重復記錄，只保留user_id字段值最大的記錄

DELETE 
FROMsys_user 
WHEREuser_id NOT IN (SELECTmaxid FROM( SELECT MAX( user_id ) AS maxid, CONCAT( user_name, email ) AS nameAndEmail FROM sys_user GROUP BY nameAndEmail ) t )

結果:

7. 完全重復數據刪除

1.查詢表完全重復的紀錄,即表行數據是完全重復
select distinct * from sys_user;
2. 如果該表需要刪除重復的記錄（重復記錄保留1條）select distinct * into sys_user1 from sys_user;drop table sys_user;select * into sys_user1 from sys_user1;drop table sys_user1;

8. 關鍵字段重復刪除

1 創建一個臨時表，用于存儲要刪除的重復數據。CREATE TABLE sys_user1 as(SELECT MIN(user_id) as userid from sys_user GROUP BY user_name );SELECT * from sys_user1;DELETE from sys_user where user_id not in(SELECT * from sys_user1);DROP TABLE  sys_user1;