mysql查找删除重复数据并只保留一条实例详解

2019/6/30 16:32:43

编程Tag： 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查找删除重复数据并只保留一条实例详解，希望能帮助到大家，谢谢大家对本站的支持！

这篇关于mysql查找删除重复数据并只保留一条实例详解的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！

mysql查找删除重复数据并只保留一条实例详解

相关编程文章