数据库中不小心导入了很多重复的数据,想要只保留重复数据中的一条数据,其他的全部删掉:
谨慎起见,按照如下步骤基本不会出问题:
1.查询你导入的所有数据:
我是通过用户ID和操作人的ID,筛选了我导入的所有数据
共255条
select *
from security_role_user
where user_id = '114741'
and operate_user = '13052'
2.删除重复后需要保留数据的总数:
共87条
select distinct security_role_code
from security_role_user
where user_id = '114741'
and operate_user = '13052'
根据以上两条我们可以看出,我们需要删除168条数据
3.查询 包含重复数据的 security_role_code 项
select security_role_code
FROM security_role_user
where user_id = '114741'
and operate_user = '13052'
GROUP BY security_role_code HAVING COUNT(security_role_code)>1
4.我们需要保留的是有重复数据中,id较小的那些条目
将这些ID查出来:
SELECT MIN(id)
FROM security_role_user
where user_id = '114741'
and operate_user = '13052'
GROUP BY security_role_code HAVING COUNT(security_role_code)>1 )
最后,我们综合一下,我们需要查出 security_role_code 在重复项中,并且ID不在我们想要保留的项中,查询的sql是:
5.查询需要删除条目的ID
总数为168
select *
from security_role_user
where user_id = '114741'
and operate_user = '13052'
and security_role_code in
(select security_role_code
FROM security_role_user
where user_id = '114741'
and operate_user = '13052'
GROUP BY security_role_code HAVING COUNT(security_role_code)>1 )
and id not in
(SELECT MIN(id) ids
FROM security_role_user
where user_id = '114741'
and operate_user = '13052'
GROUP BY security_role_code HAVING COUNT(security_role_code)>1 )
6.在5的基础上,我们根据ID删掉这些项,那么最终的sql就是:
delete
from security_role_user
where id in (
select id
from security_role_user
where user_id = '114741'
and operate_user = '13052'
and security_role_code in
(select security_role_code
FROM security_role_user
where user_id = '114741'
and operate_user = '13052'
GROUP BY security_role_code HAVING COUNT(security_role_code)>1 )
and id not in
(SELECT MIN(id) ids
FROM security_role_user
where user_id = '114741'
and operate_user = '13052'
GROUP BY security_role_code HAVING COUNT(security_role_code)>1 )
)