表结构：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_orderswhere (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_orderswhere id in      (select min(id) from exhibition_orders  group by student_id, exhibition_id having count(*)>1) ;

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

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

select count(*) from exhibition_orderswhere id in(select idfrom exhibition_orderswhere (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 idfrom exhibition_orderswhere id in      (select min(id) from exhibition_orders  group by student_id, exhibition_id having count(*)>1))

步骤四：删除

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

# 需要删除的数据 delete from exhibition_orderswhere id in(select a.id from (select idfrom exhibition_orderswhere (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 idfrom exhibition_orderswhere 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);

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