這兩天做了一個呼叫第三方介面的小程式，因為是實時更新資料，所以請求介面的頻率就很高，這樣有時會出現往資料庫插入重複的資料，對資料庫造成壓力也不方便管理，因為要透過原生sql語句，解決資料庫的去重問題.在過程中遇到了麻煩，最終解決了分享出來。

select * from cqssc group by expect having count(expect) > 1

注意：這是查出所有重複記錄的第一條記錄，需要保留，因此需要新增查詢條件，查出這三條的重複記錄

select id,expect from cqssc where expect in (select expect from cqssc group by expect having count(expect)>1)

and id not in(select min(id) from cqssc group by expect having count(expect)>1)

delete from cqssc where id in (select id from (select id from cqssc where expect in

(select expect from cqssc group by expect having count(expect)>1) and id not in

(select min(id) from cqssc group by expect having count(expect)>1)) as tmpresult)

再執行試試

可以戳原文看看：https://www.cnblogs.com/jdbeyond/p/8157224.html

DELETE

FROM

table_name AS ta

WHERE

ta.唯一鍵 <> (

SELECT

t.maxid

FROM

( SELECT max( tb.唯一鍵 ) AS maxid FROM table_name AS tb WHERE ta.判斷重複的列 = tb.判斷重複的列 ) t

);