栏目表 t_fw_channel ,字段:ID,NAME,PARENT_ID,LEVEL_ID
--根据ID 获取栏目路径:
select substr(SYS_CONNECT_BY_PATH(name,'>'),2) PATH
from t_fw_channel c where is_valid = 1
start with id = 4
connect by prior c.parent_id = c.id
--根据字段最大值 进行累计 每次+100
select nvl(max(order_id),'0')+100 from t_fw_channel
--遍历完整树:
select *
from t_fw_channel a
where is_valid = 1 --判断是否可用
and audit_status = 1 --判断状态是否通过
start with a.parent_id = 0
connect by prior a.id = a.parent_id order siblings by order_id asc;
--根据3级栏目ID 遍历树(向上遍历):
select *
from (select distinct *
from (select *
from t_fw_channel a
where is_valid = 1
and audit_status = 1
start with a.id in (select ID from t_fw_channel where LEVEL_ID=3)
connect by prior a.parent_id = a.id))
start with parent_id = 0 connect by prior id = parent_id
order siblings by order_id asc
--根据1级栏目ID 遍历树(只能头部向下遍历):
select *
from (select distinct *
from (select *
from t_fw_channel a
where is_valid = 1
and audit_status = 1
start with a.id in (select ID from t_fw_channel where LEVEL_ID=1)
connect by prior a.id=a.parent_id))
start with parent_id = 0 connect by prior id = parent_id
order siblings by order_id asc