mysql數(shù)據(jù)庫刪除重復(fù)數(shù)據(jù)只保留一條方法實(shí)例
1.問題引入
假設(shè)一個場景，一張用戶表，包含3個字段。id，identity_id，name。現(xiàn)在身份證號identity_id和姓名name有很多重復(fù)的數(shù)據(jù)，需要刪除只保留一條有效數(shù)據(jù)。
2.模擬環(huán)境
1.登入mysql數(shù)據(jù)庫，創(chuàng)建一個單獨(dú)的測試數(shù)據(jù)庫mysql_exercise
create database mysql_exercise charset utf8;
2.創(chuàng)建用戶表users
create table users(
id int auto_increment primary key,
identity_id varchar(20),
name varchar(20) not null
);
3.插入測試數(shù)據(jù)
insert into users values(0,'620616199409206512','張三'), (0,'620616199409206512','張三'), (0,'62062619930920651X','李四'), (0,'62062619930920651X','李四'), (0,'620622199101206211','王五'), (0,'620622199101206211','王五'), (0,'322235199909116233','趙六');
可以多執(zhí)行幾次，生成較多重復(fù)數(shù)據(jù)。
4.解決思路
（1）根據(jù)身份證號和name進(jìn)行分組；
（2）取出分組后的最大id（或最小id）；
（3）刪除除最大（或最小）id以外的其他字段；
5.第一次嘗試(失敗!!!)
delete from users where id not in (select max(id) from users group by identity_id,name);
報(bào)錯：
1093 (HY000): You can't specify target table 'users' for update in FROM clause
因?yàn)樵贛YSQL里，不能先select一個表的記錄，再按此條件進(jìn)行更新和刪除同一個表的記錄。
解決辦法是，將select得到的結(jié)果，再通過中間表select一遍，這樣就規(guī)避了錯誤，
這個問題只出現(xiàn)于mysql，mssql和oracle不會出現(xiàn)此問題。
所以我們可以先將括號里面的sql語句先拿出來，先查到最大（或最?。﹊d。
select max_id from (select max(id) as max_id from users group by identity_id,name);
接著，又報(bào)錯了！！！
ERROR 1248 (42000): Every derived table must have its own alias
意思是說：提示說每一個衍生出來的表，必須要有自己的別名！
執(zhí)行子查詢的時候，外層查詢會將內(nèi)層的查詢當(dāng)做一張表來處理，所以我們需要給內(nèi)層的查詢加上別名
繼續(xù)更正：
給查詢到的最大（或最小id）結(jié)果當(dāng)做一張新的表，起別名t，并查詢t.mix_id。
select t.max_id from (select max(id) as max_id from users group by identity_id,name) as t;
可以成功查到最大（或最?。﹊d了，如下圖：
6.第二次嘗試（成功?。。。?/strong>
delete from users where id not in ( select t.max_id from (select max(id) as max_id from users group by identity_id,name) as t );
執(zhí)行結(jié)果：
成功將重復(fù)的數(shù)據(jù)刪除，只保留了最后一次增加的記錄。同理也可以保留第一次添加的記錄（即刪除每個分組里面除最小id以外的其他條記錄）
3.知識拓展一：更新數(shù)據(jù)
其他場景應(yīng)用：要將用戶表user_info里名字(name)為空字符串("")的用戶的狀態(tài)(status)改成"0"
update user_info set status='0' where user_id in (select user_id from user_info where name='')
同樣報(bào)了如下錯誤：
You can't specify target table ‘user_info' for update in FROM clause
因?yàn)樵贛YSQL里，不能先select一個表的記錄，再按此條件進(jìn)行更新和刪除同一個表的記錄，解決辦法是，將select得到的結(jié)果，再通過中間表select一遍，這樣就規(guī)避了錯誤。
以下兩種均可?。。?/p>
update user_info set status='0' where user_id in (select user_id from (select user_id from user_info where name = '') t1);
下面這種也可，細(xì)微差別，別名可帶as可不帶，t1.user_id 直接和內(nèi)層的user_id對應(yīng)也可以。
update user_info set status='0' where user_id in (select t1.user_id from (select user_id from user_info where name='') as t1);
3.1 分步驟解析
（1）將以下查詢結(jié)果作為中間表：
select user_id from user_info where name='';
（2）再查詢一遍中間表作為結(jié)果集：
select user_id from (select user_id from user_info where name='') as t;
（3）更新數(shù)據(jù)
update user_info set status='0' where user_id in (select user_id from (select user_id from user_info where name='') as t1);
4.拓展練習(xí)：刪除重復(fù)數(shù)據(jù)
編寫一個 SQL 查詢，來刪除 Person 表中所有重復(fù)的電子郵箱，重復(fù)的郵箱里只保留 Id 最小 的那個。
+----+------------------+ | Id | Email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | | 3 | john@example.com | +----+------------------+
Id 是這個表的主鍵。
例如，在運(yùn)行你的查詢語句之后，上面的 Person 表應(yīng)返回以下幾行:
+----+------------------+ | Id | Email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | +----+------------------+
解答一：
delete from Person where Id not in ( select t.min_id from ( select min(Id) as min_id from Person group by Email ) as t );
解答二：
delete p1 from Person as p1,Person as p2 where p1.Email=p2.Email and p1.Id > p2.Id;
總結(jié)
到此這篇關(guān)于mysql數(shù)據(jù)庫刪除重復(fù)數(shù)據(jù)的方法只保留一條的文章就介紹到這了,更多相關(guān)mysql刪除重復(fù)數(shù)據(jù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
windows 安裝解壓版 mysql5.7.28 winx64的詳細(xì)教程
這篇文章主要介紹了windows 安裝解壓版 mysql5.7.28 winx64的詳細(xì)教程,本文通過圖文并茂的形式給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2020-12-12
這篇文章主要介紹了淺談mysql執(zhí)行過程以及順序，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-05-05
淺談MYSQL中樹形結(jié)構(gòu)表3種設(shè)計(jì)優(yōu)劣分析與分享
在開發(fā)中經(jīng)常遇到樹形結(jié)構(gòu)的場景，本文將以部門表為例對比幾種設(shè)計(jì)的優(yōu)缺點(diǎn)，具有一定的參考價值，感興趣的小伙伴們可以參考一下2021-09-09
MySQL 邏輯備份與恢復(fù)測試的相關(guān)總結(jié)
數(shù)據(jù)庫邏輯備份就是備份軟件按照我們最初所設(shè)計(jì)的邏輯關(guān)系，以數(shù)據(jù)庫的邏輯結(jié)構(gòu)對象為單位，將數(shù)據(jù)庫中的數(shù)據(jù)按照預(yù)定義的邏輯關(guān)聯(lián)格式一條一條生成相關(guān)的文本文件，以達(dá)到備份的目的。本文將具體介紹MySQL 邏輯備份的相關(guān)概念及如何做恢復(fù)測試。2021-05-05
我相信平時大家一定會遇到忘記密碼的時候，實(shí)在記不起來了，我們就會重置密碼，最快最直接的方法，這篇文章就是主要介紹了linux系統(tǒng)中重置mysql的root密碼的方法，需要的朋友可以參考下2015-08-08
這篇文章主要介紹了Mysql數(shù)據(jù)庫 ALTER 操作詳解的相關(guān)資料,需要的朋友可以參考下2022-09-09
這篇文章主要介紹了MySQL選錯索引的原因以及解決方案，幫助大家更好的理解和使用MySQL索引，感興趣的朋友可以了解下2020-10-10
這篇文章主要介紹了mysql如何對String類型的數(shù)字值排序問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-08-08
解決Mysql的left join無效及使用的注意事項(xiàng)說明
這篇文章主要介紹了解決Mysql的left join無效及使用的注意事項(xiàng)說明，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2021-07-07
在本篇內(nèi)容里小編給大家整理了關(guān)于MySQL大小寫敏感導(dǎo)致的問題的詳細(xì)分析，有興趣的朋友們可以學(xué)習(xí)參考下。2019-06-06