我有这样一张桌子：
+---------+--------+ | EMP_ID | MGR_iD | +---------+--------+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 2 | | 6 | 2 | | 7 | 3 | | 8 | 5 | | 9 | 7 | | 10 | 5 | | 11 | 7 | | 12 | 9 | | 13 | 9 | | 14 | 9 | +---------+--------+
我正在尝试解析此相邻列表以生成以下结果集：
| EMP_ID | MGR_ID | LV | LEVEL1 | LEVEL2 | LEVEL3 | LEVEL4 | LEVEL5 | --------------------------------------------------------------------- | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | | 2 | 1 | 2 | 1 | 2 | 2 | 2 | 2 | | 3 | 1 | 2 | 1 | 3 | 3 | 3 | 3 | | 4 | 2 | 3 | 1 | 2 | 4 | 4 | 4 | | 5 | 2 | 3 | 1 | 2 | 5 | 5 | 5 | | 6 | 2 | 3 | 1 | 2 | 6 | 6 | 6 | | 7 | 3 | 3 | 1 | 3 | 7 | 7 | 7 | | 8 | 5 | 4 | 1 | 2 | 5 | 8 | 8 | | 9 | 7 | 4 | 1 | 3 | 7 | 9 | 9 | | 10 | 5 | 4 | 1 | 2 | 5 | 10 | 10 | | 11 | 7 | 4 | 1 | 3 | 7 | 11 | 11 | | 12 | 9 | 5 | 1 | 3 | 7 | 9 | 12 | | 13 | 9 | 5 | 1 | 3 | 7 | 9 | 13 | | 14 | 9 | 5 | 1 | 3 | 7 | 9 | 14 |
这是我到目前为止所取得的成就：
create table PC ( EMP_ID NUMBER NULL,MGR_ID NUMBER NULL ); INSERT INTO PC (EMP_ID,MGR_ID) VALUES (1.0,1.0); INSERT INTO PC (EMP_ID,MGR_ID) VALUES (2.0,MGR_ID) VALUES (3.0,MGR_ID) VALUES (4.0,2.0); INSERT INTO PC (EMP_ID,MGR_ID) VALUES (5.0,MGR_ID) VALUES (6.0,MGR_ID) VALUES (7.0,3.0); INSERT INTO PC (EMP_ID,MGR_ID) VALUES (8.0,5.0); INSERT INTO PC (EMP_ID,MGR_ID) VALUES (9.0,7.0); INSERT INTO PC (EMP_ID,MGR_ID) VALUES (10.0,MGR_ID) VALUES (11.0,MGR_ID) VALUES (12.0,9.0); INSERT INTO PC (EMP_ID,MGR_ID) VALUES (13.0,MGR_ID) VALUES (14.0,9.0);
和查询：
WITH PERSON_HIER AS ( SELECT 1 as level1,CAST(NULL AS NUMBER) as level2,CAST(NULL AS NUMBER) as level3,CAST(NULL AS NUMBER) as level4,CAST(NULL AS NUMBER) as level5 FROM PC WHERE EMP_ID = 1 AND MGR_ID = 1 UNION ALL SELECT L1.EMP_ID AS LEVEL1,L2.EMP_ID AS LEVEL2,L3.EMP_ID AS LEVEL3,L4.EMP_ID AS LEVEL4,L5.EMP_ID AS LEVEL5 FROM PC L1 LEFT OUTER JOIN PC L2 ON (L1.EMP_ID = L2.MGR_ID AND L2.EMP_ID != L1.EMP_ID) LEFT OUTER JOIN PC L3 ON (L2.EMP_ID = L3.MGR_ID) LEFT OUTER JOIN PC L4 ON (L3.EMP_ID = L4.MGR_ID) LEFT OUTER JOIN PC L5 ON (L4.EMP_ID = L5.MGR_ID) WHERE L1.MGR_ID = L1.EMP_ID ) SELECT level1,coalesce(level2,level1) AS LEVEL2,coalesce(level3,level2,level1) AS LEVEL3,coalesce(level4,level3,level1) AS LEVEL4,coalesce(level5,level4,level1) AS LEVEL5 FROM PERSON_HIER order by level5
我正在使用Oracle 10g,因此在Oracle 10g中无法实现递归CTE或Hierarchial查询
解决方法
现在改进版本(显示正确的层次)：
select emp_id,mgr_id,lvl,h,nvl(substr(h,instr(h,'/',1,2)+1,3)- instr(h,2)-1),emp_id) as lvl1,3)+1,4)- instr(h,3)-1),emp_id) as lvl2,4)+1,5)- instr(h,4)-1),emp_id) as lvl3,5)+1,6) -instr(h,5)-1),emp_id) as lvl4,6)+1,7) -instr(h,6)-1),emp_id) as lvl5 from( select emp_id,level lvl,sys_connect_by_path(mgr_id,'/')||'/' h from pc connect by nocycle prior emp_id = mgr_id start with emp_id = 1 ) order by emp_id; EMP_ID MGR_ID LVL H LVL1 LVL2 LVL3 LVL4 LVL5 2 1 1 1/1/ 1 2 2 2 2 3 1 1 1/1/ 1 3 3 3 3 4 2 2 2/1/2/ 1 2 4 4 4 5 2 2 2/1/2/ 1 2 5 5 5 6 2 2 2/1/2/ 1 2 6 6 6 7 3 2 3/1/3/ 1 3 7 7 7 8 5 3 5/1/2/5/ 1 2 5 8 8 9 7 3 7/1/3/7/ 1 3 7 9 9 10 5 3 5/1/2/5/ 1 2 5 10 10 11 7 3 7/1/3/7/ 1 3 7 11 11 12 9 4 9/1/3/7/9/ 1 3 7 9 12 13 9 4 9/1/3/7/9/ 1 3 7 9 13 14 9 4 9/1/3/7/9/ 1 3 7 9 14 15 14 5 14/1/3/7/9/14/ 1 3 7 9 14
这是我的第一次尝试：
select emp_id,' ',1),2)- instr(h,1)),2),2)),3),3)),4),4)),5),5)),' ') h from pc connect by nocycle prior emp_id = mgr_id start with emp_id = 1 ) order by emp_id;
