postgre 向上向下组织树查询

1.根据当前登录用户ID查询出对应组织机构ID，根据该组织ID向上查询，查询出org_type=2的数据

with recursive p1 as
                           (select t1.*
                            from work_org t1
                            WHERE t1.id in(select ext18 from e_user where user_id = '1764')   -- 当前处理人对应的组织机构ID
                            union all
                            select t2.*
                            from work_org t2 inner join p1
                            on t2.id = p1.parent_id)
              select id,name,org_type as orgType ,parent_id as parentId from p1 where org_type = '2'

 根据组织ID向上查询出所有数据

 

 

根据上方查出出来的组织机构ID向下查询出所有数据

with recursive p1 as
                           (select t1.*
                            from work_org t1
                            WHERE t1.id = #{id}  -- 上级组织机构编码
                            union all
                            select t2.*
                            from work_org t2 inner join p1
                            on t2.parent_id = p1.id)
           select id,name,org_type "orgType",parent_id "parentId" from p1