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)
    
  • 1
    点赞
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值