我有兩張桌子：
表_A
pos_id res_id bb_id bsk_name
1122 10000 1444 type_1
1122 10000 5678 type_2
1122 10001 1444 type_1
1122 10001 5678 type_2
1122 10002 1467 type_1
1122 10002 5678 type_2
1122 10003 1467 type_1
1122 10003 5678 type_2
表_b
pos_id row_id bb_id bsk_name
1122 1 1444 type_1
1122 1 5678 type_2
1122 2 1467 type_1
1122 2 5678 type_2
我想加入 table_a 和 table_b 以獲取每個 res_id 的 row_id。
res_id 10000 和 10001 必須有 row_id 1 和 res_id 10002 和 10003 必須有 row_id 2。但由于沒有唯一的列來連接這兩個表，我得到了 bb_id 5678 的重復值，因為它們在兩個表中都是相同的row_id 的。
那么有沒有一種方法可以將 bb_id 與 table_a 中的 erg_id 和 table_b 中的 row_id 一起列出來連接這兩個表？
uj5u.com熱心網友回復：
您可以使用CAST(COLLECT(...) AS ...)將行聚合到用戶定義的集合中，然后比較這些集合。
首先，創建一個嵌套表型別的集合：
CREATE TYPE int_list AS TABLE OF INT;
然后你可以使用：
SELECT a.pos_id,
a.res_id,
a.bb_id,
a.bsk_name,
b.row_id,
b.bsk_name
FROM (
SELECT a.*,
CAST(
COLLECT(bb_id) OVER (PARTITION BY pos_id, res_id)
AS int_list
) AS all_bb_ids
FROM table_a a
) a
INNER JOIN (
SELECT b.*,
CAST(
COLLECT(bb_id) OVER (PARTITION BY pos_id, row_id)
AS int_list
) AS all_bb_ids
FROM table_b b
) b
ON ( a.pos_id = b.pos_id
AND a.all_bb_ids = b.all_bb_ids
AND a.bb_id = b.bb_id)
其中，對于您的示例資料：
CREATE TABLE TABLE_A ( pos_id, res_id, bb_id, bsk_name ) AS
SELECT 1122, 10000, 1444, 'type_1' FROM DUAL UNION ALL
SELECT 1122, 10000, 5678, 'type_2' FROM DUAL UNION ALL
SELECT 1122, 10001, 1444, 'type_1' FROM DUAL UNION ALL
SELECT 1122, 10001, 5678, 'type_2' FROM DUAL UNION ALL
SELECT 1122, 10002, 1467, 'type_1' FROM DUAL UNION ALL
SELECT 1122, 10002, 5678, 'type_2' FROM DUAL UNION ALL
SELECT 1122, 10003, 1467, 'type_1' FROM DUAL UNION ALL
SELECT 1122, 10003, 5678, 'type_2' FROM DUAL;
CREATE TABLE table_b (pos_id, row_id, bb_id, bsk_name) AS
SELECT 1122, 1, 1444, 'type_1' FROM DUAL UNION ALL
SELECT 1122, 1, 5678, 'type_2' FROM DUAL UNION ALL
SELECT 1122, 2, 1467, 'type_1' FROM DUAL UNION ALL
SELECT 1122, 2, 5678, 'type_2' FROM DUAL;
輸出：
POS_ID RES_ID BB_ID BSK_NAME ROW_ID BSK_NAME 1122 10000 1444 型別_1 1 型別_1 1122 10000 5678 型別_2 1 型別_2 1122 10001 1444 型別_1 1 型別_1 1122 10001 5678 型別_2 1 型別_2 1122 10002 1467 型別_1 2 型別_1 1122 10002 5678 型別_2 2 型別_2 1122 10003 1467 型別_1 2 型別_1 1122 10003 5678 型別_2 2 型別_2
db<>在這里擺弄
更新
您還可以使用LISTAGG聚合資料；但是，如果聚合字串將超過 4000 位元組，則聚合將失敗（使用CAST(COLLECT(...) ...)沒有此限制，但需要您創建集合資料型別）：
SELECT a.pos_id,
a.res_id,
a.bb_id,
a.bsk_name,
b.row_id,
b.bsk_name
FROM (
SELECT a.*,
LISTAGG(bb_id, ',') WITHIN GROUP (ORDER BY bb_id)
OVER (PARTITION BY pos_id, res_id) AS all_bb_ids
FROM table_a a
) a
INNER JOIN (
SELECT b.*,
LISTAGG(bb_id, ',') WITHIN GROUP (ORDER BY bb_id)
OVER (PARTITION BY pos_id, row_id) AS all_bb_ids
FROM table_b b
) b
ON ( a.pos_id = b.pos_id
AND a.all_bb_ids = b.all_bb_ids
AND a.bb_id = b.bb_id)
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/328332.html