【oracle】【sql】oracle去重

本文介绍了在Oracle数据库中实现数据去重的方法,包括使用distinct关键字、rowid结合关联条件、rowid结合group by以及row_number()窗口函数等四种方式,并通过示例展示了如何查看去重后的数据及删除重复数据。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >


20150225_Oracle_去重.sql
参考:
http://wenku.baidu.com/view/0a362b2003d8ce2f006623e4.html


create table t_distinct(
c1 integer,c2 varchar2(50)
);

select t.*,t.rowid from t_distinct t;

--1. distinct
--查看 去重后数据
select distinct t.* from t_distinct t;
--删除 重复数据
create table t_distinct_tmp as select distinct t.* from t_distinct t;
drop table t_distinct;
alter table t_distinct_tmp rename to t_distinct;

--2. rowid + 关联条件
--查看 去重后数据
select t1.* from t_distinct t1
where t1.rowid = (
select min(t2.rowid) from t_distinct t2 where t2.c1 = t1.c1 and t2.c2 = t2.c1
);
--删除 重复数据
delete from t_distinct t1
where t1.rowid <> (
select min(t2.rowid) from t_distinct t2 where t2.c1 = t1.c1 and t2.c2 = t2.c1
);

--3. rowid + group by
--查看 去重后数据
select t1.* from t_distinct t1
where t1.rowid in (
select min(t2.rowid) from t_distinct group by t2.c1,t2.c2
);
--删除 重复数据
delete from t_distinct t1
where t1.rowid not in (
select min(t2.rowid) from t_distinct group by t2.c1,t2.c2
);


--4. row_number() over(partition by order by)
--查看 去重后数据
select * from (
select t.*,row_number() over(partition by c1,c2 order by c1) rn from t_distinct t
) where rn = 1;
--删除 重复数据
delete from t_distinct where rowid not in (
select rid from (
select t.*,t.rowid rid,row_number() over(partition by c1,c2 order by c1) rn from t_distinct t
) where rn = 1
);

 

补充:
伪劣 rownum: 全表 排序,无间断无重复
函数 row_number() over(partition by order by): 组内 排序,无间断无重复
函数 dense_rank() over(partition by order by): 组内 排序,无间断有重复
函数 rank() over(partition by order by): 组内 排序,有间断有重复

create table t_depart_emp(
depart_no varchar2(20),
emp_name varchar2(50),
emp_salary number(9,2)
);

select t.*,t.rowid from t_depart_emp t;

select
depart_no,emp_name,emp_salary,
rownum,
row_number() over(partition by depart_no order by emp_salary) rn,
dense_rank() over(partition by depart_no order by emp_salary) drk,
rank() over(partition by depart_no order by emp_salary) rk
from t_depart_emp
order by depart_no,emp_salary;

posted on 2018-04-05 16:45  绿Z 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/greenZ/p/8723240.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值