我有 2 個 sql 資料集：
資料集 1：
select * from (
Select 1 rn, 'b' c, 3 Val from dual UNION ALL
Select 1 rn, 'r' c, 3 Val from dual UNION ALL
Select 1 rn, 'w' c, 3 Val from dual UNION ALL
Select 2 rn, 'b' c, 2 Val from dual UNION ALL
Select 2 rn, 'r' c, 2 Val from dual UNION ALL
Select 3 rn, 'b' c, 1 Val from dual
) t1
資料集2：
select * from (
Select 'b' c from dual UNION ALL
Select 'r' c from dual UNION ALL
Select 'w' c from dual ) t2
我需要獲得以下資料集：
rn c val
1 b 3
1 r 3
1 w 3
2 b 2
2 r 2
2 w null
3 b 1
3 r null
3 w null
我嘗試使用以下 sql 執行此操作，但不起作用。
select t1.*, t2.*
FROM (
Select 1 rn, 'b' c, 3 Val from dual UNION ALL
Select 1 rn, 'r' c, 3 Val from dual UNION ALL
Select 1 rn, 'w' c, 3 Val from dual UNION ALL
Select 2 rn, 'b' c, 2 Val from dual UNION ALL
Select 2 rn, 'r' c, 2 Val from dual UNION ALL
Select 3 rn, 'b' c, 1 Val from dual
) t1 right join (
Select 'b' c from dual UNION ALL
Select 'r' c from dual UNION ALL
Select 'w' c from dual ) t2 on t1.c=t2.c
order by 1,2
請幫我解決我的查詢。我使用 Oracle 資料庫。
uj5u.com熱心網友回復：
借助這兩個表之間的交叉連接（以便您獲得所有[rn, c]組合；這是我的tempCTE），然后將其外部連接到t1，您將得到結果。
第 1 - 14 行的樣本資料；您可能感興趣的查詢從第 15 行開始（只需在它前面加上WITH關鍵字）：
SQL> with t1 as (
2 Select 1 rn, 'b' c, 3 Val from dual UNION ALL
3 Select 1 rn, 'r' c, 3 Val from dual UNION ALL
4 Select 1 rn, 'w' c, 3 Val from dual UNION ALL
5 Select 2 rn, 'b' c, 2 Val from dual UNION ALL
6 Select 2 rn, 'r' c, 2 Val from dual UNION ALL
7 Select 3 rn, 'b' c, 1 Val from dual
8 ),
9 t2 as (
10 Select 'b' c from dual UNION ALL
11 Select 'r' c from dual UNION ALL
12 Select 'w' c from dual
13 ),
14 --
15 temp as
16 (select distinct a.rn, b.c
17 from t1 a cross join t2 b
18 )
19 select t.rn, t.c, a.val
20 from temp t left join t1 a on a.rn = t.rn and a.c = t.c
21 order by t.rn, t.c;
RN C VAL
---------- - ----------
1 b 3
1 r 3
1 w 3
2 b 2
2 r 2
2 w
3 b 1
3 r
3 w
9 rows selected.
SQL>
uj5u.com熱心網友回復：
這是磁區外連接的確切用例。對于大型資料集，它應該比該CROSS JOIN方法更有效。
語法是這樣的：
select t1.rn,
t2.c,
t1.val
from t1 partition by (rn) right join t2 on t2.c = t1.c;
該PARTITION BY關鍵字告訴 Oracle 為 的每個不同值重復外連接t1.rn。
這是一個完整的例子：
with t1 as (
Select 1 rn, 'b' c, 3 Val from dual UNION ALL
Select 1 rn, 'r' c, 3 Val from dual UNION ALL
Select 1 rn, 'w' c, 3 Val from dual UNION ALL
Select 2 rn, 'b' c, 2 Val from dual UNION ALL
Select 2 rn, 'r' c, 2 Val from dual UNION ALL
Select 3 rn, 'b' c, 1 Val from dual
),
t2 as (
Select 'b' c from dual UNION ALL
Select 'r' c from dual UNION ALL
Select 'w' c from dual
)
select t1.rn,
t2.c,
t1.val
from t1 partition by (rn) right join t2 on t2.c = t1.c;
---- --- ----- | RN | C | VAL | ---- --- ----- | 1 | b | 3 | | 1 | r | 3 | | 1 | w | 3 | | 2 | b | 2 | | 2 | r | 2 | | 2 | w | - | | 3 | b | 1 | | 3 | r | - | | 3 | w | - | ---- --- -----
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/402971.html
標籤：
上一篇：如何在java中加入兩個地圖？