- 论坛徽章:
- 0
|
不知道是不是最好的方案
create table t1 (a number, b number)
insert into table t1 values (1, 2)
insert into table t1 values (2, 1)
insert into table t1 values (3, 1)
insert into table t1 values (1, 3)
insert into table t1 values (6, 6)
insert into table t1 values (6, 6)
insert into table t1 values (5, 4)
create table t2 as select * from t1 where 1=2
alter table t2 add (dummy number)
merge into t2
using (select distinct a, b from t1 where a <= b) tmp on (tmp.a = t2.a and tmp.b = t2.b)
when matched then update set t2.dummy = 0
when not matched then insert (a, b) values (tmp.a, tmp.b)
-- 3 rows merged
select * from t2
1 2
6 6
1 3
merge into t2
using (select distinct b, a from t1 where a > b) tmp on (tmp.b = t2.a and tmp.a = t2.b)
when matched then update set t2.dummy = 0
when not matched then insert (a, b) values (tmp.b, tmp.a)
-- 3 rows merged
select * from t2
1 2 0
6 6
1 3 0
4 5
最后结果:
select a, b from t2 |
|