解决mysql数据重复问题-只保留一条数据

<pre name="code" class="html">1.sql语句,原理使用临时表法,效率比较低
delete
  FROM `source`
  WHERE is_deleted=0
          AND (student_id,score_id) IN
      (SELECT f.student_id ,
           f.score_id
      FROM
          (SELECT b.`student_id` AS student_id ,
           b.`score_id` AS score_id
          FROM `source` b
          WHERE b.`is_deleted`=0
          GROUP BY  b.student_id,b.score_id
          HAVING count(*) > 1) f )
              AND id NOT IN
      (SELECT g.id
      FROM
          (SELECT min(c.id) AS id
          FROM `source` c
          WHERE c.`is_deleted`=0
          GROUP BY  c.`student_id`,c.`score_id`
          HAVING count(*)>1) g );

2.临时表法-需要dba协作
1.停服情况下,原表(备份),建一张临时表,将原表的唯一数据拷贝到临时表中,删除源表,更改临时表表名
2.非停服情况下去重,新建一张临时表
insert into temp () values (select distinct * from source)
delete from source where 重复数据 in (select * from temp)
insert into source () values(select * from temp)



                
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值