Блог

Я хочу получить все родительские элементы данной записи, а затем получить все дочерние записи этих родительских записей.

Например: если таблица представляет собой что-то вроде,

таблица 1:

 Child_Id  | Parent_Id
---------------------
    23           4
    23           5
    4            20
    20           21
    5            12
    12           15
    12           17
    24           30
    39            4
  

Учитывая вышеизложенное, таблица и идентификатор равны 23, тогда я должен получить,

 21
   20 
      4 
        23  39
15
   12
      5
        23

17 
   12
      5
        23
  

Я пытался подключиться к предыдущему, но это не помогло мне,

 SELECT * FROM TABLE1 
CONNECT BY PRIOR CHILD_ID = PARENT_ID
  

Есть ли какой-либо способ сначала получить всех родителей данного узла, а затем получить всех дочерних элементов его родителей вместе с братьями и сестрами?

Вы можете использовать следующее, чтобы получить список родительских / корневых элементов:

 select parent_id as parent
  from tbl x
 where not exists (select 1 from tbl y where y.child_id = x.parent_id)

| PARENT |
|--------|
|     15 |
|     21 |
|     17 |
|     30 |
  

http://sqlfiddle.com /#!4/62c37/18/0

А затем для данного корня вы можете выполнить следующее:

 select 21 as parent,
       listagg(child_id, ' >>> ') within group(order by level) as children
  from tbl
 start with parent_id = 21
connect by prior child_id = parent_id


| PARENT |               CHILDREN |
|--------|------------------------|
|     21 | 20 >>> 4 >>> 23 >>> 39 |
  

http://sqlfiddle.com /#!4/62c37/35/0

Или, если вы не хотите переносить дочерние элементы в одну строку, вы можете получить вывод, аналогичный тому, что вы дали, выполнив:

 select 21 as parent,
       lpad(child_id,level*level,' ') as child
  from tbl
 start with parent_id = 21
connect by prior child_id = parent_id


| PARENT |     CHILD |
|--------|-----------|
|     21 |         2 |
|     21 |         4 |
|     21 |        23 |
|     21 |        39 |
  

http://sqlfiddle.com /#!4/62c37/34/0

Я не уверен, что смогу запустить его для всех корней в одном запросе, но решил, что, по крайней мере, дам вам это в ожидании другого ответа.

Однако, если в каждом из них всегда будет всего несколько дочерних элементов (например, до 10, скажем), то это должно быть выполнимо, не полагаясь на CONNECT BY и вместо этого используя подзапросы.

В качестве примера такого подхода, следующий шаг имеет глубину 8 уровней и должен работать во всей вашей таблице до тех пор, пока не будет более 8 дочерних элементов (вы можете добавить больше дочерних элементов, если это не так):

 with lvl1 as
 (select x.parent_id, x.child_id
    from tbl x
   where not exists (select 1 from tbl y where y.child_id = x.parent_id)),
lvl2 as
 (select x.parent_id, x.child_id
    from tbl x
    left join lvl1
      on x.parent_id = lvl1.child_id),
lvl3 as
 (select x.parent_id, x.child_id
    from tbl x
    left join lvl2
      on x.parent_id = lvl2.child_id),
lvl4 as
 (select x.parent_id, x.child_id
    from tbl x
    left join lvl3
      on x.parent_id = lvl3.child_id),
lvl5 as
 (select x.parent_id, x.child_id
    from tbl x
    left join lvl4
      on x.parent_id = lvl4.child_id),
lvl6 as
 (select x.parent_id, x.child_id
    from tbl x
    left join lvl5
      on x.parent_id = lvl5.child_id),
lvl7 as
 (select x.parent_id, x.child_id
    from tbl x
    left join lvl6
      on x.parent_id = lvl6.child_id),
lvl8 as
 (select x.parent_id, x.child_id
    from tbl x
    left join lvl7
      on x.parent_id = lvl7.child_id)
select  parent_id,
        case when lag(child2,1) over (partition by parent_id order by parent_id) = child2 then null else child2 end as child2,
        case when lag(child3,1) over (partition by parent_id order by parent_id) = child3 then null else child3 end as child3,
        case when lag(child4,1) over (partition by parent_id order by parent_id) = child4 then null else child4 end as child4,
        case when lag(child5,1) over (partition by parent_id order by parent_id) = child5 then null else child5 end as child5,
        case when lag(child6,1) over (partition by parent_id order by parent_id) = child6 then null else child6 end as child6,
        case when lag(child7,1) over (partition by parent_id order by parent_id) = child7 then null else child7 end as child7,
        case when lag(child8,1) over (partition by parent_id order by parent_id) = child8 then null else child8 end as child8
from(
select distinct
       lvl1.parent_id,
       lvl2.parent_id as child2,
       nvl(lvl3.parent_id,lvl2.child_id) as child3,
       nvl(lvl4.parent_id,lvl3.child_id) as child4,
       nvl(lvl5.parent_id,lvl4.child_id) as child5,
       nvl(lvl6.parent_id,lvl5.child_id) as child6,
       nvl(lvl7.parent_id,lvl6.child_id) as child7,
       nvl(lvl8.parent_id,lvl7.child_id) as child8
  from lvl1
  left join lvl2
    on lvl1.child_id = lvl2.parent_id
  left join lvl3
    on lvl2.child_id = lvl3.parent_id
  left join lvl4
    on lvl3.child_id = lvl4.parent_id
  left join lvl5
    on lvl4.child_id = lvl5.parent_id
  left join lvl6
    on lvl5.child_id = lvl6.parent_id
  left join lvl7
    on lvl6.child_id = lvl7.parent_id
  left join lvl8
    on lvl7.child_id = lvl8.parent_id
order by parent_id)
  

http://sqlfiddle.com /#!4/62c37/50/0

 | PARENT_ID | CHILD2 | CHILD3 | CHILD4 | CHILD5 | CHILD6 | CHILD7 | CHILD8 |
|-----------|--------|--------|--------|--------|--------|--------|--------|
|        15 |     12 |      5 |     23 | (null) | (null) | (null) | (null) |
|        17 |     12 |      5 |     23 | (null) | (null) | (null) | (null) |
|        21 |     20 |      4 |     23 | (null) | (null) | (null) | (null) |
|        21 | (null) | (null) |     39 | (null) | (null) | (null) | (null) |
|        30 | (null) | (null) | (null) | (null) | (null) | (null) | (null) |