表结构: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);
你还有什么其他方法么?欢迎留言
声明:本站资源来自会员发布以及互联网公开收集,不代表本站立场,仅限学习交流使用,请遵循相关法律法规,请在下载后24小时内删除。 如有侵权争议、不妥之处请联系本站删除处理! 请用户仔细辨认内容的真实性,避免上当受骗!