重复数据只显示一条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)

原理和上面一样。