程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 編程語言 >> 網頁編程 >> PHP編程 >> 關於PHP編程 >> mysql查找刪除重復數據並只保留一條實例詳解,mysql詳解

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

編輯:關於PHP編程

mysql查找刪除重復數據並只保留一條實例詳解,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查找刪除重復數據並只保留一條實例詳解,希望能幫助到大家,謝謝大家對本站的支持!

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved