表结构: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联系删除