加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 (https://www.lidatong.com.cn/)- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置: 首页 > 百科 > 正文

Oracle重复数据相关

发布时间:2020-12-12 15:42:12 所属栏目:百科 来源:网络整理
导读:Oracle中对重复数据的查询、删除 1、查找重复记录,根据id判断 select * from table where id in (select id from table group by id having count(id) 1); 2、删除多余重复记录,保留rowid最小记录,根据id判断 DELETE from table WHERE (id) IN ( SELECT i

Oracle中对重复数据的查询、删除


1、查找重复记录,根据id判断

select * from table where id in (select id from table group by id having count(id) > 1);

2、删除多余重复记录,保留rowid最小记录,根据id判断

DELETE from table WHERE (id) IN ( SELECT id FROM table GROUP BY id HAVING COUNT(id) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM table GROUP BY id HAVING COUNT(*) > 1);

3、查找重复记录,根据多个字段判断

select * from table a where (a.id,a.id2) in(select Id,seq from table group by id,id2 having count(*) > 1);

4、删除重复记录,保留rowid最小记录,根据多个字段判断

delete from table a where (a.id,a.id2) in (select id,id2 from table group by id,id2 having count(*) > 1) and rowid not in (select min(rowid) from table group by id,id2 having count(*)>1);

5、查找重复记录,不包含rowid最小记录,根据多个字段判断

select * from table a where (a.id,a.id2) in (select Id,id2 having count(*)>1);

(编辑:李大同)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    推荐文章
      热点阅读