组织机构树查询

组织机构树递归查询

查询父级节点的所有子节点

select organizational_Id,organizational_Name,parent_Id from sys_organizational where is_used = '1' start with parent_id='父级节点id' connect by prior organizational_id=parent_Id （不包含父级节点）

select organizational_Id,organizational_Name,parent_Id from sys_organizational where is_used = '1' start with organizational_Id='父级节点id' connect by prior organizational_id=parent_Id （包含父级节点）

通过子节点向根节点追朔.

select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid

可通过level 关键字查询所在层次.

select a.*,level from persons.dept a start with paredeptid=0 connect by prior deptid=paredeptid

查询组织机构的所有末级节点

select organizational_id from (
select t."organizational_id",t."organizational_NAME",t."parent_id",nvl(t2.isParent,0) isParent from sys_organizational t 
LEFT JOIN (
SELECT count(1) isParent,parent_id from sys_organizational GROUP BY parent_id
) t2
on t.organizational_id = T2.parent_id 
) where isParent = 0

查询组织机构的所有父级节点

select organizational_id from (
select t."organizational_id",t."organizational_NAME",t."parent_id",nvl(t2.isParent,0) isParent from sys_organizational t 
LEFT JOIN (
SELECT count(1) isParent,parent_id from sys_organizational GROUP BY parent_id
) t2
on t.organizational_id = T2.parent_id 
) where isParent = 0