sql - 递归自连接的可变级别 - ORACLE

Question

我有下表:

+----+---------+----------------+| ID | COMPANY | PARENT_COMPANY |+----+---------+----------------+|  1 | A       | X              ||  2 | B       | Y              ||  3 | C       | Z              ||  4 | D       | A              ||  5 | E       | D              |+----+---------+----------------+

我需要检索某个公司和所有后代，无论它们有多少个以及它们扩展了多少级别。

例如，公司 A 有 child D，后者又有 child E 等等。我想在一栏中显示所有这些公司。

我厌倦了 self 加入，但我认为它的级别数是固定的。我这里查了ORACLE相关问题，发现this但层数有限制。

Answer 1

使用 WITH 子句:

WITH rt (
    COMPANY,
    CHILD_COMPANY
) AS (
    SELECT
        t.COMPANY,
        t.COMPANY AS CHILD_COMPANY
    FROM
        Test t
    WHERE
        t.COMPANY = 'A' /* selected company */
UNION ALL
    SELECT
        rt.COMPANY,
        t.COMPANY AS CHILD_COMPANY
    FROM
        rt
    JOIN
        Test t
    ON
        rt.CHILD_COMPANY = t.PARENT_COMPANY
)
SELECT
    rt.COMPANY,
    LISTAGG(rt.CHILD_COMPANY, ', ') WITHIN GROUP (ORDER BY rt.CHILD_COMPANY) AS DESCENDANTS
FROM
    rt
WHERE
    rt.COMPANY <> rt.CHILD_COMPANY  /* exclude self-match */
GROUP BY
    rt.COMPANY;