ORALCE SQL 遍历树

栏目表  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