Allgemeine Struktur

with recursive t(n) as (
  values (1)
  union all
  select n+1 from t where n < 100
)
select sum(n) from t;

Daten

• Tabelle tree0, p: Parent, v: Value
• Werte gibt es nur bei den Blättern

Rekursive Abfrage

with recursive
  tree1(id, p, v, lvl, pth) as (
    select id, p, v, 1, cast(id as varchar(200))
    from tree0
    where p is null
  union all
    select t0.id, t0.p, t0.v, lvl+1, cast(pth || '/' || t0.id as varchar(200))
    from tree0 t0 join tree1 t1 on t0.p=t1.id
  ),
  tree2(id, p, v, lvl, pth, kind) as (
    select 
      id, p, v, lvl, pth,
      case
        when lvl=(select min(lvl) from tree1) then 'root'
        when (not exists (select * from tree1 below where below.p =tree1.id)) then 'leaf'
        else 'inner'
      end
    from tree1
  )
select id, p, v, lvl, pth, kind
from tree2
order by lvl;