欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

mysql查找刪除重復數(shù)據(jù)并只保留一條實例詳解

 更新時間：2016年09月24日 08:33:28   投稿：lqh  
這篇文章主要介紹了mysql查找刪除重復數(shù)據(jù)并只保留一條實例詳解的相關資料,需要的朋友可以參考下

有這樣一張表，表數(shù)據(jù)及結果如下：

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的字段值有重復數(shù)據(jù)(Abraham Lincoln High School 和Agoura High School分別出現(xiàn)兩次)，那么如何刪除這兩條數(shù)據(jù)，從而只讓這兩個數(shù)值出現(xiàn)一次呢？ 具體實現(xiàn)方法如下：

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語法查詢出重復的數(shù)據(jù)，然后刪除重復數(shù)據(jù)并保留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查找刪除重復數(shù)據(jù)并只保留一條實例詳解，希望能幫助到大家，謝謝大家對本站的支持！

相關文章

最新評論