sql去除重复数据

表结构:exhibition_orders表有三个属性:id,student_id, exhibition_id。

需求:相同(student_id, exhibition_id) 保留最小id的一条数据。

分析思路

查找所有的重复数据 ,设为X查找所有的重复数据中最小的一行数据,设为Y在X中,排除Y部分数据,就是需要删除的数据

具体步骤

步骤一: 查找所有的重复数据

方法一

select count(*)
from exhibition_orders eo
         join (select student_id, exhibition_id
               from exhibition_orders
               group by student_id, exhibition_id
               having count(*) > 1) eo2 on eo2.student_id = eo.student_id and eo2.exhibition_id = eo.exhibition_id

方法二

select count(*)
from exhibition_orders
where (exhibition_id, student_id) in
      (select exhibition_id, student_id from exhibition_orders  group by student_id, exhibition_id having count(*)>1) ;

步骤二:重复数据中最小编号

# 重复数据中最小编号
select count(*)
from exhibition_orders
where id in
      (select min(id) from exhibition_orders  group by student_id, exhibition_id having count(*)>1) ;

步骤三:查询中需要删除的数据

在X中,排除Y部分数据,就是需要删除的数据

select count(*) from exhibition_orders
where id in(select id
from exhibition_orders
where (exhibition_id, student_id) in
      (select exhibition_id, student_id from exhibition_orders  group by student_id, exhibition_id having count(*)>1))
and id not in(select id
from exhibition_orders
where id in
      (select min(id) from exhibition_orders  group by student_id, exhibition_id having count(*)>1))

步骤四:删除

删除表是当前表,记得给表取别名

# 需要删除的数据 
delete from exhibition_orders
where id in(select a.id from (select id
from exhibition_orders
where (exhibition_id, student_id) in
      (select exhibition_id, student_id from exhibition_orders  group by student_id, exhibition_id having count(*)>1)) as a)

and id not in(select b.id from (select id
from exhibition_orders
where id in
      (select min(id) from exhibition_orders  group by student_id, exhibition_id having count(*)>1)) as b) ;

其他方法

创建唯一索引,源头控制

create unique index exhibition_orders_exhibition_id_student_id_uindex
    on exhibition_orders (exhibition_id, student_id);

你还有什么其他方法么?欢迎留言

本文来自投稿,不代表重蔚自留地立场,如若转载,请注明出处https://www.cwhello.com/48927.html

如有侵犯您的合法权益请发邮件951076433@qq.com联系删除

(0)
潘144660514潘144660514订阅用户
上一篇 2022年7月5日 23:23
下一篇 2022年7月5日 23:23

相关推荐

联系我们

QQ:951076433

在线咨询:点击这里给我发消息邮件:951076433@qq.com工作时间:周一至周五,9:30-18:30,节假日休息