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