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

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

分析思路

  1. 查找所有的重复数据 ,设为X
  2. 查找所有的重复数据中最小的一行数据,设为Y
  3. 在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);

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

加客服微信:qiushu0517,开通VIP下载权限!