《PHP實(shí)戰(zhàn)：mysql查找刪除重復(fù)數(shù)據(jù)并只保留一條實(shí)例詳解》要點(diǎn)：
本文介紹了PHP實(shí)戰(zhàn)：mysql查找刪除重復(fù)數(shù)據(jù)并只保留一條實(shí)例詳解，希望對(duì)您有用。如果有疑問(wèn)，可以聯(lián)系我們。

有這樣一張表,表數(shù)據(jù)及結(jié)果如下：PHP實(shí)戰(zhàn)

我們可以看出,school_name的字段值有重復(fù)數(shù)據(jù)(Abraham Lincoln High School 和Agoura High School分別出現(xiàn)兩次),那么如何刪除這兩條數(shù)據(jù),從而只讓這兩個(gè)數(shù)值出現(xiàn)一次呢? 具體實(shí)現(xiàn)方法如下：PHP實(shí)戰(zhàn)

1、刪除重復(fù)記錄,保存Id最小的一條PHP實(shí)戰(zhàn)

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語(yǔ)法查詢出重復(fù)的數(shù)據(jù),然后刪除重復(fù)數(shù)據(jù)并保留school_id最小的一條.PHP實(shí)戰(zhàn)

2、刪除重復(fù)記錄,保存Id最大的一條PHP實(shí)戰(zhàn)

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)

原理和上面一樣.PHP實(shí)戰(zhàn)

以上就是mysql查找刪除重復(fù)數(shù)據(jù)并只保留一條實(shí)例詳解,希望能幫助到大家,謝謝大家對(duì)本站的支持!PHP實(shí)戰(zhàn)

轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.snjht.com/jiaocheng/3222.html