具备条件是:系统拥有多级部门,每级部门中拥有自己的order_num,从XXX~999,但是每级部门的order_num并不相关联
要求是给一个unit_id,查询此unit下的所有子部门和孙子部门和重孙子部门...(此处省略一亿字)
select
unit_id,
unit_code,
SUPERIOR_UNIT_ID,
concat(superior_unit_name,'/',unit_name) unit_name,
level_code,
remark,
order_num,
UNIT_TYPE,
org_type
from UNIT
START WITH (UNIT_ID = '传入的unit_id')
CONNECT BY SUPERIOR_UNIT_ID = PRIOR UNIT_ID
AND
invalid = '0'
order siblings by order_num asc
从传入的unit向下递归搜索
order silblings by 可以使父亲节点挨着其子节点