POSTGRESQL 递归树查询

postgresql递归关键字 with recursive r_name as (select ***)

使用递归查询可以实现数据的树形查询，代码以下

with recursive r as (
    select id, 
	   parent_id,
	   ARRAY[id] AS path,
	   '' sub_level,
	   1 dept_level
      from hr_department
     where parent_id is null
    union all
    select hu.id,  
	   hu.parent_id,
	   r.path||hu.id as path,
	   r.sub_level || '    ' as sub_level,
	   r.dept_level + 1 as dept_level
      from hr_department hu, r 
     where hu.parent_id = r.id)
	
select hd.id, r.sub_level || hd.name as dept_name, r.dept_level 
  from hr_department hd, r 
 where hd.id = r.id
 order by r.path

查询效果：