mysql查找删除重复数据并只保留一条

小咪咪 2022-10-23 08:05 127阅读 0赞

mysql表中，某一字段的值有很多重复数据，需要将这些重复的数据记录删除，只保留其中的一条数据即可，如何使用SQL语句来实现呢，本文章向大家介绍mysql如何查找删除重复数据，需要的朋友可以参考一下。

有这样一张表，表数据及结果如下：

<table> 
 <tbody> 
  <tr> 
   <td>school_id</td> 
   <td>school_name</td> 
   <td>total_student</td> 
   <td>test_takers</td> 
  </tr> 
  <tr> 
   <td>1239</td> 
   <td>Abraham Lincoln High School</td> 
   <td>55</td> 
   <td>50</td> 
  </tr> 
  <tr> 
   <td>1240</td> 
   <td>Abraham Lincoln High School</td> 
   <td>70</td> 
   <td>35</td> 
  </tr> 
  <tr> 
   <td>1241</td> 
   <td>Acalanes High School</td> 
   <td>120</td> 
   <td>89</td> 
  </tr> 
  <tr> 
   <td>1242</td> 
   <td>Academy Of The Canyons</td> 
   <td>30</td> 
   <td>30</td> 
  </tr> 
  <tr> 
   <td>1243</td> 
   <td>Agoura High School</td> 
   <td>89</td> 
   <td>40</td> 
  </tr> 
  <tr> 
   <td>1244</td> 
   <td>Agoura High School</td> 
   <td>100</td> 
   <td>50</td> 
  </tr> 
 </tbody> 
</table>

我们可以看出，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)

原理和上面一样。

[GROUP BY having]: http://www.manongjc.com/article/1065.html