sql 去掉重复的数据

数据库中不小心导入了很多重复的数据，想要只保留重复数据中的一条数据，其他的全部删掉：

谨慎起见，按照如下步骤基本不会出问题：

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 )
)