如何连接或列出同一列中的两个值，以便将其与其他列中的值连接起来

Question

我有两个表:

TABLE_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

table_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's中是相同的。

那么有没有一种方法像listagg的bb_id's与erg_id在table_a和row_id在table_b加入这两个表?

Answer 1

您可以使用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_IDRES_IDBB_IDBSK_NAMEROW_IDBSK_NAMEtbody><<tr>1122100001444type_11type_11122100005678type_21type_21122100011444type_11type_11122100015678type_21type_21122100021467type_12type_11122100025678type_22type_21122100031467type_12type_11122100035678type_22type_2