Mysql 整理重复数据

淩亂°似流年 2022-12-02 01:27 97阅读 0赞

表结构：

<table> 
 <thead> 
  <tr> 
   <th>school_id</th> 
   <th>school_name</th> 
   <th>total_student</th> 
   <th>test_takers</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1239</td> 
   <td>河南大学</td> 
   <td>80</td> 
   <td>50</td> 
  </tr> 
  <tr> 
   <td>1240</td> 
   <td>河南大学</td> 
   <td>60</td> 
   <td>65</td> 
  </tr> 
  <tr> 
   <td>1241</td> 
   <td>武汉工业大学</td> 
   <td>102</td> 
   <td>58</td> 
  </tr> 
  <tr> 
   <td>1242</td> 
   <td>四川农业大学</td> 
   <td>98</td> 
   <td>85</td> 
  </tr> 
  <tr> 
   <td>1243</td> 
   <td>浙江理工大学</td> 
   <td>66</td> 
   <td>70</td> 
  </tr> 
  <tr> 
   <td>1244</td> 
   <td>浙江理工大学</td> 
   <td>85</td> 
   <td>59</td> 
  </tr> 
 </tbody> 
</table>

注⚠️：新建表格 字段无需上引号 插入语句 字段如果是字符类型记得上引号

**新建表：**

create table `test`(school\_id int,school\_name varchar(255),total\_student int,test\_takers int);

**插入数据：**

insert into `test`(school\_id,`school_name`,total\_student,test\_takers) values(1239,“河南大学”,80,50),(1240,“河南大学”,60,65),(1241,“武汉工业大学”,102,58),(1242,“四川农业大学”,98,85),(1243,“浙江理工大学”,66,70),(1244,“浙江理工大学”,85,59);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdmdWxvbmc2NjY_size_16_color_FFFFFF_t_70_pic_center]

**删除所有重复数据：**

delete from test where school\_name in (select a.school\_name from (select school\_name from test group by school\_name having count(\*)>1) **a**);

**查询重复项中最大的id：**

select max(school\_id) from test group by school\_name having count(\*)>1;

**删除重复数据中id最大的数据：**

delete from test where school\_id in (select \* from (select max(school\_id) from test group by school\_name having count(\*)>1) **as ori**);

\****使用ori（可指定任意名称） 承接查询出的数据作为中间表 否则会查询出错*** \*

**删除除了最大id外的重复数据：**

delete from test where school\_id **not in** (select \* from (select max(school\_id) from test group by school\_name having count(\*)>1) as ori);

=======================================

**出现的错误**

**1.** only\_full\_group\_by ：

group by 中必须包含 要查询的字段  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdmdWxvbmc2NjY_size_16_color_FFFFFF_t_70_pic_center 1]

**2.** you can’t specify target table ‘tablename’ for update in from clause

**mysql 不能对同一个表同时进行 update(delete) 和 select 联合操作**  
如果非得要操作 那只能 把 条件中 再关联一次（不能的缘由是因为 条件中的查询是 在 临时表中查的，保存却是原表，解决方法就是将查询和保存都放在临时就可以了）

将SELECT出的结果再通过中间表SELECT一遍，这样就规避了错误。![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdmdWxvbmc2NjY_size_16_color_FFFFFF_t_70_pic_center 2]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdmdWxvbmc2NjY_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/c83ab672241a414ca1159e06589bb982.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdmdWxvbmc2NjY_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221123/04955976dede46a48013be00d9a04724.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdmdWxvbmc2NjY_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221123/43d490a95d4f44339ae7579ed0f85570.png