本篇文章给大家带来了关于oracle的相关知识，其中主要介绍了关于数据清理的时候常常会清除表中的重复的数据,那么在oracle中怎么处理呢？下面一起来看一下，希望对大家有帮助。
推荐教程：《Oracle视频教程》
创建测试数据
create table nayi224_180824(col_1 varchar2(10), col_2 varchar2(10), col_3 varchar2(10)); insert into nayi224_180824 select 1, 2, 3 from dual union all select 1, 2, 3 from dual union all select 5, 2, 3 from dual union all select 10, 20, 30 from dual ; commit; select*from nayi224_180824;
| COL_1 | COL_2 | COL_3 |
|---|---|---|
| 1 | 2 | 3 |
| 1 | 2 | 3 |
| 5 | 2 | 3 |
| 10 | 20 | 30 |
针对指定列，查出去重后的结果集
distinct
select distinct t1.* from nayi224_180824 t1;
| COL_1 | COL_2 | COL_3 |
|---|---|---|
| 10 | 20 | 30 |
| 1 | 2 | 3 |
| 5 | 2 | 3 |
方法局限性很大，因为它只能对全部查询的列做去重。如果我想对col_2,col3去重，那我的结果集中就只能有col_2,col_3列，而不能有col_1列。
select distinct t1.col_2, col_3 from nayi224_180824 t1
| COL_2 | COL_3 |
|---|---|
| 2 | 3 |
| 20 | 30 |
不过它也是最简单易懂的写法。
row_number()
select *
from (select t1.*,
row_number() over(partition by t1.col_2, t1.col_3 order by 1) rn
from nayi224_180824 t1) t1
where t1.rn = 1
;| COL_1 | COL_2 | COL_3 | RN |
|---|---|---|---|
| 1 | 2 | 3 | 1 |
| 10 | 20 | 30 | 1 |
写法上要麻烦不少，但是有更大的灵活性。
针对指定列，查出所有重复的行
count having
select *
from nayi224_180824 t
where (t.col_2, t.col_3) in (select t1.col_2, t1.col_3
from nayi224_180824 t1
group by t1.col_2, t1.col_3
having count(1) > 1)| COL_1 | COL_2 | COL_3 |
|---|---|---|
| 1 | 2 | 3 |
| 1 | 2 | 3 |
| 5 | 2 | 3 |
要查两次表，效率会比较低。不推荐。
count over
select *
from (select t1.*,
count(1) over(partition by t1.col_2, t1.col_3) rn
from nayi224_180824 t1) t1
where t1.rn > 1
;| COL_1 | COL_2 | COL_3 | RN |
|---|---|---|---|
| 1 | 2 | 3 | 3 |
| 1 | 2 | 3 | 3 |
| 5 | 2 | 3 | 3 |
只需要查一次表，推荐。
删除所有重复的行
delete from nayi224_180824 t
where t.rowid in (
select rid
from (select t1.rowid rid,
count(1) over(partition by t1.col_2, t1.col_3) rn
from nayi224_180824 t1) t1
where t1.rn > 1);就是上面的语句稍作修改。
删除重复数据并保留一条
分析函数法
delete from nayi224_180824 t
where t.rowid in (select rid
from (select t1.rowid rid,
row_number() over(partition by t1.col_2, t1.col_3 order by 1) rn
from nayi224_180824 t1) t1
where t1.rn > 1);拥有分析函数一贯的灵活性高的特点。可以为所欲为的分组，并通过改变orderby从句来达到像”保留最大id“这样的要求。
group by
delete from nayi224_180824 t
where t.rowid not in
(select max(rowid) from nayi224_180824 t1 group by t1.col_2, t1.col_3);牺牲了一部分灵活性，换来了更高的效率。
推荐教程：《Oracle视频教程》
