oracle數(shù)據(jù)庫(kù)去除重復(fù)數(shù)據(jù)常用的方法總結(jié)
創(chuàng)建測(cè)試數(shù)據(jù)
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 |
針對(duì)指定列，查出去重后的結(jié)果集
distinct
select distinct t1.* from nayi224_180824 t1;
| COL_1 | COL_2 | COL_3 |
|---|---|---|
| 10 | 20 | 30 |
| 1 | 2 | 3 |
| 5 | 2 | 3 |
方法局限性很大，因?yàn)樗荒軐?duì)全部查詢的列做去重。如果我想對(duì)col_2,col3去重，那我的結(jié)果集中就只能有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 |
不過(guò)它也是最簡(jiǎn)單易懂的寫(xiě)法。
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 |
寫(xiě)法上要麻煩不少，但是有更大的靈活性。
針對(duì)指定列，查出所有重復(fù)的行
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 |
要查兩次表，效率會(huì)比較低。不推薦。
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 |
只需要查一次表，推薦。
刪除所有重復(fù)的行
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);
就是上面的語(yǔ)句稍作修改。
刪除重復(fù)數(shù)據(jù)并保留一條
分析函數(shù)法
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);
擁有分析函數(shù)一貫的靈活性高的特點(diǎn)。可以為所欲為的分組，并通過(guò)改變orderby從句來(lái)達(dá)到像”保留最大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);
犧牲了一部分靈活性，換來(lái)了更高的效率。
總結(jié)
到此這篇關(guān)于oracle數(shù)據(jù)庫(kù)去除重復(fù)數(shù)據(jù)常用的文章就介紹到這了,更多相關(guān)oracle去除重復(fù)數(shù)據(jù)內(nèi)容請(qǐng)搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！
版權(quán)聲明：本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái)，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信