id parent_id name
300000001 0 供电公司
320000012 300000001 安全监察部
320000028 300000001 变电检修工区
320000069 320000028 变电维修二班
320000068 320000028 变电维修一班1、我现在根据变电维修一班的ID:320000068查询结果是这样的:供电公司-》变电检修工区-》变电维修一班
这样差结果是查不到:
select * from t_department start with parent_id=320000069 connect by prior parent_id=id
2、还要整个表的层次结构
怎么查能体现数的层次结构????
300000001 0 供电公司
320000012 300000001 安全监察部
320000028 300000001 变电检修工区
320000069 320000028 变电维修二班
320000068 320000028 变电维修一班1、我现在根据变电维修一班的ID:320000068查询结果是这样的:供电公司-》变电检修工区-》变电维修一班
这样差结果是查不到:
select * from t_department start with parent_id=320000069 connect by prior parent_id=id
2、还要整个表的层次结构
怎么查能体现数的层次结构????
select * from t_department start with id=320000069 connect by prior parent_id=id
from t_department
start with id = '320000068'
connect by prior parent_id = id
from t_department
start with id = '320000068'
connect by prior id = parent_id
FROM (SELECT t.id, t.name
FROM t
START WITH t.id = 320000069
CONNECT BY t.id = PRIOR parent_id
ORDER BY LEVEL);
SELECT wmsys.wm_concat(id), REPLACE(wmsys.wm_concat(NAME), ',', '->')
FROM (SELECT t.id, t.name
FROM t
START WITH t.id = 320000069
CONNECT BY t.id = PRIOR parent_id
ORDER BY LEVEL DESC);