Firebirdforum.de

Code: Alles auswählen

with recursive
  tree as (
    -- basis
    select b.child id, b.child parent, b.lvl, b.distanz, b.str
    from base b
    union all
    -- erweiterung
    select t.id, e.parent, t.lvl + 1, t.distanz, left(t.str, t.distanz)
    from ext e
    join tree t on t.parent = e.id),

  base as (
    select 688 child, 688 parent, 0 lvl, 0 distanz, null str from rdb$database
    union all
    select 689 child, 688 parent, 0 lvl, 1 distanz, 'aaa' from rdb$database),

  ext as (
    select child id, parent, distanz, str
    from base
    where distanz > 0)

select parent, id child, lvl, distanz, distanz - lvl dl, str
from tree
order by parent, id, distanz