删除重复的数据

• 查询存在重复的数据

select role_id,user_id from (select ru.role_id,ru.user_id,count(0) cc from sys_role_user ru 
 group by ru.role_id,ru.user_id) where cc>1

• 删除存在重复的数据

利用 row_number() over (partition by [分组字段名] order by [排序字段名]) as [序号列名]
来定位需要删除的数据

 delete from sys_role_user sru where sru.role_user_id in
 (select role_user_id from (
 select ru2.*,row_number() over (partition by ru2.role_id,ru2.user_id order by ru2.user_id)sn from sys_role_user ru2
 where (ru2.role_id,ru2.user_id) in(
 select role_id,user_id from (select ru.role_id,ru.user_id,count(0) cc from sys_role_user ru 
 group by ru.role_id,ru.user_id) where cc>1)
 order by ru2.role_id,ru2.user_id
 ) where sn=1)