CREATE TABLE T126
(
    ID NUMBER(4),
    DeptName VARCHAR2(20),
    UpDeptNo NUMBER(4)
);

INSERT INTO T126 VALUES(1, '董事会', 0);
INSERT INTO T126 VALUES(2, '总裁办', 1);
INSERT INTO T126 VALUES(3, '账务部', 1);
INSERT INTO T126 VALUES(4, '市场部', 2);
INSERT INTO T126 VALUES(5, '公关部', 2);
INSERT INTO T126 VALUES(6, '分销处', 4);

CREATE VIEW ViewT126 AS 
(SELECT ID,DeptName, s FROM
(SELECT ID, DeptName, UpDeptNo || ', ' || ID AS s
FROM T126
START WITH UpDeptNo = 1
CONNECT BY PRIOR ID = UpDeptNo
ORDER BY ID)
UNION 
SELECT ID, DeptName, to_char(UpDeptNo) AS s FROM T126 WHERE ID = 1);

------解决方案--------------------

select id,deptname,substr(decode(sys_connect_by_path(id, ','),',1',',0',sys_connect_by_path(id, ',')),2)
as updeptno from test
start with id =1
connect by prior id = updeptno
order by id

这样试试，貌似可行。。