表结构：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);

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