1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36
| SQL> WITH ta AS (
2 SELECT 'c1' AS ca, 'v1' AS va FROM dual union ALL
3 SELECT 'c1' AS ca, 'v2' AS va FROM dual union ALL
4 SELECT 'c1' AS ca, 'v3' AS va FROM dual union ALL
5 SELECT 'c2' AS ca, 'v8' AS va FROM dual ),
6 tb AS (
7 SELECT 'c1' AS cb, 'w1' AS vb FROM dual union ALL
8 SELECT 'c2' AS cb, 'w2' AS vb FROM dual union ALL
9 SELECT 'c2' AS cb, 'w3' AS vb FROM dual union ALL
10 SELECT 'c3' AS cb, 'w8' AS vb FROM dual
11 ),
12 rn_ta as (
13 select ca, va, row_number() over(partition by ca order by va) as rn
14 from ta
15 ),
16 rn_tb as (
17 select cb, vb, row_number() over(partition by cb order by vb) as rn
18 from tb
19 )
20 select coalesce(t1.ca,t2.cb) as ca_cb, t1.va, t2.vb
21 from rn_ta t1
22 full join rn_tb t2 on t2.cb = t1.ca and t2.rn = t1.rn
23 order by ca_cb, va, vb;
CA VA VB
-- -- --
c1 v1 w1
c1 v2
c1 v3
c2 v8 w2
c2 w3
c3 w8
6 rows selected.
SQL> |
Partager