oracle去除重復數據的方法：1、針對指定列，查出所有重復的行，并刪除，方法為count having；2、刪除所有重復的行，代碼為【delete from nayi224_180824 t where t.rowid in】。
本文操作環境：Windows7系統，oracle9i版本，Dell G3電腦。
oracle去除重復數據的方法：
創建測試數據
create?table?nayi224_180824(col_1?varchar2(10),?col_2?varchar2(10),?col_3?varchar2(10)); insert?into?nayi224_180824select?1,?2,?3?from?dual?union?allselect?1,?2,?3?from?dual?union?allselect?5,?2,?3?from?dual?union?allselect?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);
犧牲了一部分靈活性，換來了更高的效率。
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
