postgresql数据库 树形菜单查询

with d as (
    select
       z.dictid
      ,z.dictlv
      ,z.dicttarget
      ,z.code
      ,z.code_p
      ,z.value
      ,z.enabled
      ,0 lv
      ,to_char(z.orderid,'fm000000') fullorder
      from zd z
      where 
        code_p = '-1'
    UNION ALL
    select
       z2.dictid
      ,z2.dictlv
      ,z2.dicttarget
      ,z2.code
      ,z2.code_p
      ,z2.value
      ,z2.enabled
      ,d.lv + 1 lv
      ,d.fullorder||'|'||to_char(z2.orderid,'fm000000') fullorder
      from zd z2
        inner join d on z2.code_p = d.code
  )
  select
    *
    from d
    order by d.fullorder,d.code