站長資訊網本篇文章給大家帶來了關于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視頻教程》
