假設有一個用戶表 user，資料如下：
1、查詢表中 uid 重復的資料
SELECT id, uid, name FROM USER WHERE uid IN (SELECT uid FROM USER GROUP BY uid HAVING COUNT(uid) > 1);
2、查詢表中重復資料，排除最小id
SELECT id, uid, name FROM user WHERE uid IN (SELECT uid FROM user GROUP BY uid HAVING COUNT(uid) > 1) AND id NOT IN (SELECT MIN(id) FROM user GROUP BY uid HAVING COUNT(uid) > 1);
3、洗掉表中重復資料，如果是重復資料，則保留id最小的一條
DELETE FROM USER WHERE id IN (SELECT u.id FROM (SELECT id FROM USER WHERE uid IN (SELECT uid FROM USER GROUP BY uid HAVING COUNT(uid) > 1) AND id NOT IN (SELECT MIN(id) FROM USER GROUP BY uid HAVING COUNT(uid) > 1)) AS u);
4、遇到的問題：
一開始直接使用以下陳述句洗掉，報錯：You can’t specify target table ‘user’ for update in FROM clause
DELETE FROM USER WHERE id IN (SELECT id FROM USER WHERE uid IN (SELECT uid FROM USER GROUP BY uid HAVING COUNT(uid) > 1) AND id NOT IN (SELECT MIN(id) FROM USER GROUP BY uid HAVING COUNT(uid) > 1));
查資料后得知：
因為在 MYSQL 里，不能先 select 一個表的記錄，在按此條件進行更新和洗掉同一個表的記錄，
解決辦法：
將 select 得到的結果，再通過中間表 select 一遍，
SQL如下：
DELETE FROM USER WHERE id IN (SELECT u.id FROM (SELECT id FROM USER WHERE uid IN (SELECT uid FROM USER GROUP BY uid HAVING COUNT(uid) > 1) AND id NOT IN (SELECT MIN(id) FROM USER GROUP BY uid HAVING COUNT(uid) > 1)) AS u);
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/40017.html
標籤：MySQL
上一篇：mysqlbinlog增量恢復