mysql查找刪除重復數據並只保留一條實例詳解

有這樣一張表，表數據及結果如下：

school_id school_name total_student test_takers 1239 Abraham Lincoln High School 55 50 1240 Abraham Lincoln High School 70 35 1241 Acalanes High School 120 89 1242 Academy Of The Canyons 30 30 1243 Agoura High School 89 40 1244 Agoura High School 100 50

我們可以看出，school_name的字段值有重復數據(Abraham Lincoln High School 和Agoura High School分別出現兩次)，那麼如何刪除這兩條數據，從而只讓這兩個數值出現一次呢？ 具體實現方法如下：

1、刪除重復記錄,保存Id最小的一條

delete FROM `test` WHERE `school_name` in (SELECT `school_name`
FROM `test` 
GROUP BY `school_name` 
HAVING COUNT( * ) >1) and school_id not in (select min(school_id) from test group by school_id having count(* )>1)

先使用GROUP BY having語法查詢出重復的數據，然後刪除重復數據並保留school_id最小的一條.

2、刪除重復記錄,保存Id最大的一條

delete FROM `test` WHERE `school_name` in (SELECT `school_name`
FROM `test` 
GROUP BY `school_name` 
HAVING COUNT( * ) >1) and school_id not in (select max(school_id) from test group by school_id having count(* )>1)

原理和上面一樣。

以上就是mysql查找刪除重復數據並只保留一條實例詳解，希望能幫助到大家，謝謝大家對本站的支持！