join - 如何连接或列出同一列中的两个值以将其与另一列中的值连接
问题描述
我有两张桌子:
表_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 列出来加入这两个表?
解决方案
您可以使用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 type_1 1 type_1 1122 10000 5678 类型_2 1 类型_2 1122 10001 1444 type_1 1 type_1 1122 10001 5678 类型_2 1 类型_2 1122 10002 1467 type_1 2 type_1 1122 10002 5678 类型_2 2 类型_2 1122 10003 1467 type_1 2 type_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<>在这里摆弄