SQL 查询比较两个 varray()

Question

我有一张员工表。其中一列是一个 varray() ，其中包含他们办公室的多个房间#。我正在寻找一个简单的查询，该查询将比较每个员工以查看他们是否共用一个办公室。

SELECT  E1.Name, E2.Name
FROM    Employee E1
JOIN    Employee E2
ON      E1.Room = E2.Room;

这样的事情不起作用，因为 Room 列是一个 varray。我只需要第一个变量中的一个值与第二个变量中的另一个值匹配。有没有简单的方法来做到这一点？

Answer 1

假设您指的是 Oracle，您选择的查询可以是

select
    E1.name as employee_1, E2.name as employee_2,
    R1.column_value as the_matching_room
from employee E1
    cross join table(E1.rooms) R1
    join employee E2
        on E2.emp_id > E1.emp_id
    join table(E2.rooms) R2
        on R2.column_value = R1.column_value
;

或(更有效)

with rooms_unnested$ as (
    select E.emp_id, E.name, R.column_value as room
    from employee E
        cross join table(E.rooms) R
)
select
    E1.name as employee_1, E2.name as employee_2,
    E1.room as the_matching_room
from rooms_unnested$ E1
    join rooms_unnested$ E2
        on E2.emp_id > E1.emp_id
        and E2.room = E1.room
;

这个有一个潜在的问题，即首先在员工表之间进行笛卡尔运算，然后再取消集合的嵌套:

-----------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name     | Rows    | Bytes      | Cost   | Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |          | 1334324 | 5142484696 | 447202 | 00:00:18 |
|   1 |   NESTED LOOPS                        |          | 1334324 | 5142484696 | 447202 | 00:00:18 |
|   2 |    NESTED LOOPS                       |          |   16336 |   62926272 |     63 | 00:00:01 |
|   3 |     NESTED LOOPS                      |          |       2 |       7700 |      7 | 00:00:01 |
|   4 |      TABLE ACCESS FULL                | EMPLOYEE |       2 |       3850 |      3 | 00:00:01 |
| * 5 |      TABLE ACCESS FULL                | EMPLOYEE |       1 |       1925 |      2 | 00:00:01 |
|   6 |     COLLECTION ITERATOR PICKLER FETCH |          |    8168 |      16336 |     28 | 00:00:01 |
| * 7 |    COLLECTION ITERATOR PICKLER FETCH  |          |      82 |        164 |     27 | 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 5 - filter("E2"."EMP_ID">"E1"."EMP_ID")
* 7 - filter(VALUE(KOKBF$)=VALUE(KOKBF$))

假设您的“房间”变量可能包含重复项，还有一项调整要做——让每个员工的房间都不同，这导致我们(希望)最终查询......

with rooms_unnested$ as (
    select distinct
        E.emp_id, E.name, R.column_value as room
    from employee E
        cross join table(E.rooms) R
)
select
    E1.name as employee_1, E2.name as employee_2,
    E1.room as the_matching_room
from rooms_unnested$ E1
    join rooms_unnested$ E2
        on E2.emp_id > E1.emp_id
        and E2.room = E1.room
;

...这也恰好解决了笛卡尔函数的“问题”，方法是首先(并且只有一次!)取消嵌套“房间”变量数组，然后进行 equi-hash-join:

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                        | Rows  | Bytes  | Cost | Time     |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                             |     1 |    120 |   65 | 00:00:01 |
|   1 |   TEMP TABLE TRANSFORMATION                |                             |       |        |      |          |
|   2 |    LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6699_11FF28DD |       |        |      |          |
|   3 |     HASH UNIQUE                            |                             |     3 |     36 |   61 | 00:00:01 |
|   4 |      NESTED LOOPS                          |                             | 16336 | 196032 |   59 | 00:00:01 |
|   5 |       TABLE ACCESS FULL                    | EMPLOYEE                    |     2 |     20 |    3 | 00:00:01 |
|   6 |       COLLECTION ITERATOR PICKLER FETCH    |                             |  8168 |  16336 |   28 | 00:00:01 |
| * 7 |    HASH JOIN                               |                             |     1 |    120 |    4 | 00:00:01 |
|   8 |     VIEW                                   |                             |     3 |    180 |    2 | 00:00:01 |
|   9 |      TABLE ACCESS FULL                     | SYS_TEMP_0FD9D6699_11FF28DD |     3 |     36 |    2 | 00:00:01 |
|  10 |     VIEW                                   |                             |     3 |    180 |    2 | 00:00:01 |
|  11 |      TABLE ACCESS FULL                     | SYS_TEMP_0FD9D6699_11FF28DD |     3 |     36 |    2 | 00:00:01 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 7 - access("E2"."ROOM"="E1"."ROOM")
* 7 - filter("E2"."EMP_ID">"E1"."EMP_ID")