MySQL查詢重複記錄sql語句

常用的語句

1、尋找表中多餘的重複記錄，重複記錄是根據單個欄位（mail_id）來判斷

代碼如下	複製代碼
SELECT * FROM table WHERE mail_id IN (SELECT mail_id FROM table GROUP BY mail_id HAVING COUNT(mail_id) > 1);

2、刪除表中多餘的重複記錄，重複記錄是根據單個欄位（mail_id）來判斷，只留有rowid最小的記錄

代碼如下	複製代碼
DELETE FROM table WHERE mail_id IN (SELECT mail_id FROM table GROUP BY mail_id HAVING COUNT(mail_id) > 1) AND rowid NOT IN (SELECT MIN(rowid) FROM table GROUP BY mail_id HAVING COUNT(mail_id )>1);

3、尋找表中多餘的重複記錄（多個欄位）

代碼如下	複製代碼
SELECT * FROM table WHERE (mail_id,phone) IN (SELECT mail_id,phone FROM table GROUP BY mail_id,phone HAVING COUNT(*) > 1);

4、刪除表中多餘的重複記錄（多個欄位），只留有rowid最小的記錄

代碼如下	複製代碼
DELETE FROM table WHERE (mail_id,phone) IN (SELECT mail_id,phone FROM table GROUP BY mail_id,phone HAVING COUNT(*) > 1) AND rowid NOT IN (SELECT MIN(rowid) FROM table GROUP BY mail_id,phone HAVING COUNT(*)>1);

5、尋找表中多餘的重複記錄（多個欄位），不包含rowid最小的記錄

代碼如下	複製代碼
SELECT * FROM table WHERE (a.mail_id,a.phone) IN (SELECT mail_id,phone FROM table GROUP BY mail_id,phone HAVING COUNT(*) > 1) AND rowid NOT IN (SELECT MIN(rowid) FROM table GROUP BY mail_id,phone HAVING COUNT(*)>1);

預存程序

代碼如下	複製代碼
declare @max integer,@id integer declare cur_rows cursor local for select 主欄位,count(*) from 表名 group by 主欄位 having count(*) >； 1 open cur_rows fetch cur_rows into @id,@max while @@fetch_status=0 begin select @max = @max -1 set rowcount @max delete from 表名 where 主欄位 = @id fetch cur_rows into @id,@max end close cur_rows set rowcount 0