[大数据]MySQL删除表重复数据

MySQL删除重复数据

• 如下表
  单子段重复

  • 删除删除仅stu_name一个字段重复的数据

  -- 查询全部重复数据
  select * from tb_4 where stu_name in (select stu_name from tb_4 group by stu_name having count(1) > 1)
  -- 删除全部重复数据
  delete from tb_4 where stu_name in (select a.stu_name from (select stu_name from tb_4 group by stu_name having count(1) > 1)a)
  
  

   	![在这里插入图片描述](https://img-blog.csdnimg.cn/c2bf098b61194e77acd54fe53a9f8ddc.png#pic_center)
  

  • 部分删除（保留一行）

  -- 查询多余重复数据
  select * from tb_4 where id not in (select min(id) from tb_4 group by stu_name)
  -- 删除多余重复数据（保留一行）
  delete from tb_4 where id not in (select a.min_id from (select min(id) as min_id from tb_4 group by stu_name)a)
  

   		![在这里插入图片描述](https://img-blog.csdnimg.cn/bb698d67c0674e809f70d30f02b19c2a.png#pic_center)
  

多字段重复：

• 全部删除 （删除stu_name、score两个字段重复的数据）

  -- 查询全部重复数据
  select * from tb_4 where  stu_name in (select a.stu_name from (select stu_name from tb_4 group by stu_name, score having count(1) > 1)a)
  and score in (select a.score from (select score from tb_4 group by stu_name, score having count(1) > 1)a)
  -- 删除全部重复数据
  delete from tb_4 where stu_name in (select a.stu_name from (select stu_name from tb_4 group by stu_name, score having count(1) > 1)a)
  and score in (select a.score from (select score from tb_4 group by stu_name, score having count(1) > 1)a)
  

   ![在这里插入图片描述](https://img-blog.csdnimg.cn/c926658eb9bc47c0b4760d081aaa2005.png#pic_center)
  

• 部分删除（保留一行）

  -- 查询多余重复数据
  select * from tb_4 where id not in (select min(id) from tb_4 group by stu_name, score)
  -- 删除多余重复数据（保留一行）
  delete from tb_4 where id not in (select a.min_id from (select min(id) as min_id from tb_4 group by stu_name, score)a)
  

   		![在这里插入图片描述](https://img-blog.csdnimg.cn/f10f2284d0c449a19f667c54def40afe.png#pic_center)
  

创作打卡挑战赛

赢取流量/现金/CSDN周边激励大奖