mysql查重复数据并只保留最⼤最⼩数据
有这样⼀张表，表数据及结果如下
school_id school_name total_student test_takers
1239Abraham Lincoln High School5550
1240Abraham Lincoln High School7035
1241Acalanes High School12089
1242Academy Of The Canyons3030
1243Agoura High School8940
1244Agoura High School10050
我们可以看出，school_name的字段值有重复数据(Abraham Lincoln High School 和Agoura High School分别出现两次)，那么如何删除这两条数据，从⽽只让这两个数值出现⼀次呢？具体实现⽅法如下：
1、查重复记录，保存Id最⼩的⼀条
SELECT * FROM `test` WHERE `school_name` in (SELECT `school_name`
FROM `test`
GROUP BY `school_name`
HAVING COUNT( * ) >1) and school_id  in (select min(school_id) from test group by school_id having count(* )>1)
2、查重复记录，保存Id最⼤的⼀条
SELECT * FROM `test` WHERE `school_name` in (SELECT `school_name`
FROM `test`
GROUP BY `school_name`
HAVING COUNT( * ) >1) and school_id  in (select max(school_id) from test group by school_id having count(* )>1)mysql删除重复的数据保留一条

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。