oracle两列相同的去重
源地址:https://zhidao.baidu.com/question/66722841.html
1.不含大字段(clob等)的表格:
1
2
3
4
5
6
7
8
9
|
--例子表格:create table test(a number,b number); --方法一:通过group by + rowid,效率低 delete from test t where t.rowid not in ( select min (rowid) from test group by a, b); --方法二:通过 create + rename + distinct ,效率高 create table test_tmp as select distinct * from test t; drop table test; alter table test_tmp rename to test; |
2.含大字段(clob等)的表格:
1
2
3
4
5
6
7
8
9
10
|
--例子表格:create table test(a number,b clob); --clob 长度小于4000: select distinct t.a,to_char(t.b) as b from test t; --clob 长度大于4000: select * from test a where a.rowid = ( select max (b.rowid) from test b where b.a = a.a and nvl(dbms_lob.compare(b.b, a.b), 0) = 0); |