Oracle:比较重叠关系

Question

我想根据相关表中的行比较两条记录。假设我有两个表:FOOS 和 BARS。

FOOS

+----+
| id |
+----+
| 1  |
| 2  |
| 3  |
+----+

BARS 

+--------+-------+
| foo_id | value |
+--------+-------+
| 1      |   1   |
| 1      |   2   |
| 1      |   3   |
| 2      |   1   |
| 2      |   2   |  
| 3      |   3   |
+--------+-------+

所以每个 Foo 都有一个 bar 集合。

• Foo 1 的条形值为 1、2、3
• Foo 2 的条值为 1、2
• Foo 3 的条值为 3

我希望能够从 FOO id 中找出哪些其他 FOOS 具有相同的 id。

• Foo 1 没有任何匹配的 foo。
• Foo 2 与 Foo 1 匹配，因为 Foo 1 的条形值为 1 和 2。
• Foo 3 与 Foo 1 匹配，因为 Foo 1 的条值为 3。

我想将结果呈现为如下表格:

+---------+----------+
| src_foo | targ_foo |
+---------+----------+
|    2    |    1     |
|    3    |    1     |
+---------+----------+

<小时/>

这是提供上述数据的数据库设置脚本:

create table foos (id number(32) not null) ;
create table bars (foo_id number(32) not null, val number(32) not null);

insert into foos (id) values (1);
insert into foos (id) values (2);
insert into foos (id) values (3);

insert into bars (val, foo_id) values (1, 1);
insert into bars (val, foo_id) values (2, 1);
insert into bars (val, foo_id) values (3, 1);
insert into bars (val, foo_id) values (1, 2);
insert into bars (val, foo_id) values (2, 2);
insert into bars (val, foo_id) values (3, 3);

Answer 1

我们可以使用MULTISET运算符和COLLECT函数来做到这一点。

创建嵌套表TYPE。

CREATE OR REPLACE TYPE numtab as TABLE OF NUMBER(32);

编辑

我们可以对嵌套表使用SUBMULTISET运算符，这更简单，并且与下面的方法相同。谢谢@mathguy

WITH t AS (
     SELECT foo_id,
            CAST(COLLECT(val) AS numtab) x
     FROM bars
     GROUP BY foo_id
)   
SELECT a.foo_id a_foo_id,
       b.foo_id b_foo_id
FROM t a
CROSS JOIN t b
WHERE a.x != b.x 
AND a.x SUBMULTISET OF b.x 

另一种方法

WITH t AS (
     SELECT foo_id,
            CAST(COLLECT(val) AS numtab) x
     FROM bars
     GROUP BY foo_id
) 
select a_foo_id,b_foo_id FROM
( SELECT a.foo_id a_foo_id,
         b.foo_id b_foo_id,
         a.x a_x,
         b.x b_x,
         a.x MULTISET INTERSECT b.x as i
  FROM t a
    CROSS JOIN t b
  WHERE a.x != b.x
) where i = a_x
  ;

Demo