Таблица 1:
| id | data1 |
|---|---|
| 1 | a1 |
| 1 | b1 |
| 2 | c1 |
| 2 | d1 |
| 2 | e1 |
Таблица 2:
| id | data2 |
|---|---|
| 1 | a2 |
| 1 | b2 |
| 1 | c2 |
| 2 | d2 |
| 2 | e2 |
Необходимый результат:
| id | data1 | data2 |
|---|---|---|
| 1 | a1 | a2 |
| 1 | b1 | b2 |
| 1 | c2 | |
| 2 | c1 | d2 |
| 2 | d1 | e2 |
| 2 | e1 |
SELECT DISTINCT
COALESCE(T1.id, T2.id) AS id
,T1."data" AS data1
,T2."data" AS data2
FROM T1
FULL JOIN T2 ON T1.id = T2.id AND LEFT(T1."data", 1) = LEFT(T2."data", 1)
ORDER BY id, data1, data2
Только результат будет не как в описании, без пары будут с1 и с2:
Всем спасибо за помощь, получилось с подсказкой Akina.
select nvl(t1.ID,t2.ID) as ID,
t1.DATA,
t2.DATA
from (select ROW_NUMBER() OVER(partition by T.ID order by T.ID) as R,
T.ID,
T.DATA
from TABLE1 T) T1
full join (select ROW_NUMBER() OVER(partition by T.ID order by T.ID) as R,
T.ID,
T.DATA
from TABLE2 T) T2
on T1.r = T2.r and t1.id = t2.id