mysql實現查重只留一個的方法

154次閱讀

共計 2129 個字符，預計需要花費 6 分鐘才能閱讀完成。

自動寫代碼機器人，免費開通

這篇文章將為大家詳細講解有關 mysql 實現查重只留一個的方法，丸趣 TV 小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

mysql 實現查重只留一個的方法：首先通過“select * from”查找表中多余的重復記錄；然后通過“delete from”刪除重復數據，并只保留一個數據即可。

mysql 刪除重復數據只保留一條記錄

刪除重復數據保留 name 中 id 最小的記錄

delete from order_info where id not in (select id from (select min(id) as id from order_info group by order_number) as b);

delete from table where id not in (select min(id) from table group by name having count(name) 1) and id in (select id group by name having count(name) 1)

(注意：HAVING 子句對 GROUP BY 子句設置條件的方式與 WHERE 和 SELECT 的交互方式類似。WHERE 搜索條件在進行分組操作之前應用；而 HAVING 搜索條件在進行分組操作之后應用。HAVING 語法與 WHERE 語法類似，但 HAVING 可以包含聚合函數。HAVING 子句可以引用選擇列表中顯示的任意項。)

擴展：

SQL：刪除重復數據，只保留一條用 SQL 語句, 刪除掉重復項只保留一條在幾千條記錄里, 存在著些相同的記錄, 如何能用 SQL 語句, 刪除掉重復的呢

1、查找表中多余的重復記錄，重復記錄是根據單個字段（peopleId）來判斷

select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) 1)

2、刪除表中多余的重復記錄，重復記錄是根據單個字段（peopleId）來判斷，只留有 rowid 最小的記錄

delete from people where peopleName in (select peopleName from people group by peopleName having count(peopleName) 1) and peopleId not in (select min(peopleId) from people group by peopleName having count(peopleName) 1)

3、查找表中多余的重復記錄（多個字段）

select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) 1)

4、刪除表中多余的重復記錄（多個字段），只留有 rowid 最小的記錄

delete from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*) 1)

5、查找表中多余的重復記錄（多個字段），不包含 rowid 最小的記錄

select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*) 1)

6. 消除一個字段的左邊的第一位：

update tableName set [Title]=Right([Title],(len([Title])-1)) where Title like 村 %

7. 消除一個字段的右邊的第一位：

update tableName set [Title]=left([Title],(len([Title])-1)) where Title like % 村

8. 假刪除表中多余的重復記錄（多個字段），不包含 rowid 最小的記錄

update vitae set ispass=-1 where peopleId in (select peopleId from vitae group by peopleId,seq having count(*) 1) and seq in（select seq from vitae group by peopleId,seq having count(*) 1）and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*) 1)

關于 mysql 實現查重只留一個的方法就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。

向 AI 問一下細節

丸趣 TV 網 – 提供最優質的資源集合！

正文完