Максимальный 3 раза иерархический запрос к таблице самообъединения

У меня есть БД Oracle с таблицей со следующими столбцами:

    ID | PARENTID | DETAIL1
    ------------------------
    1  | NULL     | BLAH1
    2  | 1        | BLAH2
    3  | 2        | BLAH3
    4  | 2        | BLAH4
    5  | NULL     | BLAH5
    6  | 5        | BLAH6
    7  | 6        | BLAH7
    8  | 5        | BLAH8
    9  | 5        | BLAH9
    10 | 8        | BLAH10

Я подготовил самостоятельное объединение для

    SELECT    PARENT.ID AS "PID",
              PARENT.DETAIL1 AS "PDETAIL1",
              CHILD.ID AS "CID",
              CHILD.DETAIL1 AS "CDETAIL1" 

      FROM    table1 CHILD

      LEFT OUTER JOIN table1 PARENT

      ON      PARENT.ID = CHILD.PARENTID
      WHERE   PARENTID IS NOT NULL;

Вывод выглядит так, как показано ниже:

    PID | PDETAIL1 | CID | CDETAIL1|
    --------------------------------
    1   | BLAH1    | 2   | BLAH2   |
    2   | BLAH2    | 3   | BLAH3   |
    2   | BLAH2    | 4   | BLAH4   |
    5   | BLAH5    | 6   | BLAH6   |
    6   | BLAH6    | 7   | BLAH7   |
    5   | BLAH5    | 8   | BLAH8   |
    5   | BLAH5    | 9   | BLAH9   |
    8   | BLAH8    | 10  | BLAH10  |

Довольно прямо вперед. Я хотел бы знать, может ли это самостоятельное соединение быть выполнено как иерархический / рекурсивный запрос. Максимальная глубина вложения равна 3. Целевой вывод должен выглядеть следующим образом:

    GPID | GPDETAIL1 | PID | PDETAIL1 | CID  | CDETAIL1 |
    ---------------------------------------------------
    1    | BLAH1     | 2   | BLAH2    | 3    | BLAH3    |
    1    | BLAH1     | 2   | BLAH2    | 4    | BLAH4    |
    5    | BLAH5     | 6   | BLAH6    | 7    | BLAH7    |
    5    | BLAH5     | 8   | BLAH8    | 10   | BLAH10   |
    5    | BLAH5     | 9   | BLAH9    | NULL | NULL     |

Google не помогает мне, есть тонна информации, связанной с иерархическими запросами, но ничего, включая самостоятельные объединения и иерархические запросы, и большинство вопросов, кажется, похожи (на первый взгляд), но ничто не ведет меня к тому, что мне нужно. Я новичок в SQL, поэтому, если ответ не является конкретным, я могу его пропустить.

Источник

2 ответа

Решение

Существует масса информации, относящейся к иерархическим запросам, но ничего, включая самостоятельные соединения И иерархические запросы

Вам не нужно и то и другое, иерархический запрос - это самообъединение.

Вы можете приблизиться, начав с иерархического запроса, например:

select connect_by_root (id) as gpid, connect_by_root(detail1) as gpdetail1,
  prior id as pid, prior detail1 as pdetail1,
  id as cid, detail1 as cdetail1,
  level as lvl, connect_by_isleaf as is_leaf
from table1
start with parentid is null
connect by prior id = parentid

Посмотрите документы, чтобы узнать, что означают connect_by_root и connect_by_isleaf. Вы заинтересованы в листовых узлах, но это:

select *
from (
  select connect_by_root (id) as gpid, connect_by_root(detail1) as gpdetail1,
    prior id as pid, prior detail1 as pdetail1,
    id as cid, detail1 as cdetail1,
    level as lvl, connect_by_isleaf as is_leaf
  from table1
  start with parentid is null
  connect by prior id = parentid
)
where is_leaf = 1;

... не совсем то, что вы хотите:

      GPID GPDETA        PID PDETAI        CID CDETAI        LVL    IS_LEAF
---------- ------ ---------- ------ ---------- ------ ---------- ----------
         1 BLAH1           2 BLAH2           3 BLAH3           3          1
         1 BLAH1           2 BLAH2           4 BLAH4           3          1
         5 BLAH5           6 BLAH6           7 BLAH7           3          1
         5 BLAH5           8 BLAH8          10 BLAH10          3          1
         5 BLAH5           5 BLAH5           9 BLAH9           2          1

Из вашего примера вывода вы не хотите 5/BLAH5 в родительских столбцах последней строки, так как они являются бабушкой и дедушкой; Вы хотите, чтобы дочерние значения были переведены в родительский статус. Вы можете немного манипулировать родительскими и дочерними значениями:

select gpid, gpdetail1,
  case lvl when 2 then cid else pid end as pid,
  case lvl when 2 then cdetail1 else pdetail1 end as pdetail1,
  case lvl when 2 then null else cid end as cid,
  case lvl when 2 then null else cdetail1 end as cdetail1
from (
  select connect_by_root (id) as gpid, connect_by_root(detail1) as gpdetail1,
    prior id as pid, prior detail1 as pdetail1,
    id as cid, detail1 as cdetail1,
    level as lvl, connect_by_isleaf as is_leaf
  from table1
  start with parentid is null
  connect by prior id = parentid
)
where is_leaf = 1;

      GPID GPDETA        PID PDETAI        CID CDETAI
---------- ------ ---------- ------ ---------- ------
         1 BLAH1           2 BLAH2           3 BLAH3 
         1 BLAH1           2 BLAH2           4 BLAH4 
         5 BLAH5           6 BLAH6           7 BLAH7 
         5 BLAH5           8 BLAH8          10 BLAH10
         5 BLAH5           9 BLAH9

Но только с тремя фиксированными уровнями просто присоединиться снова проще для понимания...

Источник

Вам просто нужно join к столу еще раз, чтобы получить детей. Так что в основном есть дедушка, родительский псевдоним и дочерний псевдоним и join соответственно:

select 
    gp.id as gpid,
    gp.detail as gpdetail1,
    p.id as pid,
    p.detail as pdetail1,
    c.id as cid,
    c.detail as cdetail1
from yourtable gp
    left join yourtable p on gp.id = p.parentid
    left join yourtable c on p.id = c.parentid
where gp.parentid is null
Источник
Другие вопросы по тегам