oracle删除重复数据只保留一条
发布时间:2020-12-12 13:12:27 所属栏目:百科 来源:网络整理
导读:-- 如表role_user的数据 ROLEID USERID 15 88010000000074 15 88010000000074 15 88010000000074 -- 删除相同记录只剩下一条记录 ? 根据两个字段查询重复数据 (roleid,userid) select * from role_user a where (a.roleid,a.userid) in ( select roleid,use
-- 如表role_user的数据 ROLEID USERID 15 88010000000074 15 88010000000074 15 88010000000074 -- 删除相同记录只剩下一条记录 ? 根据两个字段查询重复数据 (roleid,userid) select * from role_user a where (a.roleid,a.userid) in(select roleid,userid from role_user group by roleid,userid having count(*) > 1) 删除重复数据只保留一条 delete from role_user where rowid not in (select min(rowid) from role_user group by roleid,userid ) ? 下面的只根据userid进行查询与删除 select count(1),USERID from role_user group by USERID HAVING count(1)>1 DELETE from role_user WHERE (userid) IN ( SELECT userid FROM role_user GROUP BY userid HAVING COUNT(userid) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM role_user GROUP BY userid HAVING COUNT(*) > 1); (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |