设为首页收藏本站

80后

 找回密码
 立即注册

扫一扫,访问微社区

QQ登录

只需一步,快速开始

查看: 1155|回复: 0

oracle查询重复数据

[复制链接]

16

主题

23

帖子

66

积分

注册会员

Rank: 2

积分
66
发表于 2017-7-25 13:30:31 | 显示全部楼层 |阅读模式
oracle查询重复数据
select * from 表 where 条件 and 判重字段 not in
(select 判重字段 from 表 where 条件 group by 判重字段 having count(*) > 1)

根据rowid删除重复数据,保留一条
delete from 表 where 条件 and 判重字段 not in
(select 判重字段 from 表 where 条件 group by 判重字段 having count(*) >1)
and rowid not in
(select max(rowid) from 表 where 条件 group by 判重字段 having count(*) >1)

eg:删除导入病案数据中的重复数据
delete from rhsa_hs4_1_2013_temp
where period = '00083'
and org_id = '370000003584'
and (bah, zycs) in (select bah, zycs
from rhsa_hs4_1_2013_temp
where period = '00083'
and org_id = '370000003584'
group by bah, zycs
having count(*) > 1)
and rowid not in (select max(rowid)
from rhsa_hs4_1_2013_temp
where period = '00083'
and org_id = '370000003584'
group by bah, zycs
having count(*) > 1)

回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

Archiver|手机版|小黑屋|www.333cm.com ( 京ICP备16037542号  

GMT+8, 2020-7-11 18:04 , Processed in 0.249998 second(s), 26 queries .

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表