oracle查询父节点及其下所有子节点

1.我们的组织机构就是这种树形菜单的格式。

.

 

2.执行sql：

select
( select organization_name from SYS_ORGANIZATION where organization_id =x.organization_id) organization_name,
c.organization_id
from SYS_ORGANIZATION c, (select organization_id from SYS_ORGANIZATION where parent_id = '1') x where x.organization_id = c.organization_id or x.organization_id = c.parent_id start with c.organization_id =x.organization_id
connect by prior c.organization_id = c.parent_id

效果：

3.执行sql：

select organization_name,wmsys.wm_concat(organization_id ) organization_ids from
(select
( select organization_name from SYS_ORGANIZATION where organization_id =x.organization_id) organization_name,
c.organization_id
from SYS_ORGANIZATION c, (select organization_id from SYS_ORGANIZATION where parent_id = '1') x where x.organization_id = c.organization_id or x.organization_id = c.parent_id start with c.organization_id =x.organization_id
connect by prior c.organization_id = c.parent_id ) a group by organization_name

效果：

 

 4.执行sql：

select a.organization_name,count(c.organization_id) sum from
(select organization_name,wmsys.wm_concat(organization_id ) organization_ids from
(select
( select organization_name from SYS_ORGANIZATION where organization_id =x.organization_id) organization_name,
c.organization_id
from SYS_ORGANIZATION c, (select organization_id from SYS_ORGANIZATION where parent_id = '1') x where x.organization_id = c.organization_id or x.organization_id = c.parent_id start with c.organization_id =x.organization_id
connect by prior c.organization_id = c.parent_id ) a group by organization_name ) a left join
(select c.clxxbh,c.organization_id from v_wfxx c left join BIZ_IMPORTANT_PLACES z on c.kkmc = z.place_name left join SYS_ORGANIZATION c on z.place_unit = c.organization_id) c
on instr(a.organization_ids,c.organization_id) > 0 group by a.organization_name

效果：