例子 create table tmp_hzf_test (id number, name varchar2(20), pid number);插入一些数据 ,如 1 A 0 2 B 1 3 C 2 4 D 3 5 E 3 6 F 4 7 G 5 8 H 1select a.*,level from tmp_hzf_test a start with id=1 connect by prior id=pid ; 执行结果 ID NAME PID LEVEL 1 A 0 1 2 B 1 2 3 C 2 3 4 D 3 4 6 F 4 5 5 E 3 4 7 G 5 5 8 H 1 2
007教程上面的实例,我也不大懂,希望对楼主有所帮组。select employeeid,lastname,firstname,reportsto from employees start with reportsto=5 connect by employeeid=reportsto;select level employeeid,lastname,firstname,reportsto from employees start with reportsto=5 connect by employeeid=reportsto;
--查找所有的自上而下的关系:领导->员工 SELECT LPAD(' ', 2 * (LEVEL - 1), '-') || ENAME P, EMPNO, MGR, JOB FROM SCOTT.EMP START WITH ENAME = 'KING' CONNECT BY PRIOR EMPNO = MGR;
select deptid, LPad(' ', (level - 1) * 36, ' ') || deptname from ptdept start with deptid in ('698') connect by prior deptid = parentdeptid
(id number,
name varchar2(20),
pid number);插入一些数据 ,如
1 A 0
2 B 1
3 C 2
4 D 3
5 E 3
6 F 4
7 G 5
8 H 1select a.*,level from tmp_hzf_test a
start with id=1
connect by prior id=pid ;
执行结果
ID NAME PID LEVEL
1 A 0 1
2 B 1 2
3 C 2 3
4 D 3 4
6 F 4 5
5 E 3 4
7 G 5 5
8 H 1 2
from employees
start with reportsto=5
connect by employeeid=reportsto;select level employeeid,lastname,firstname,reportsto
from employees
start with reportsto=5
connect by employeeid=reportsto;
--查找所有的自上而下的关系:领导->员工
SELECT LPAD(' ', 2 * (LEVEL - 1), '-') || ENAME P, EMPNO, MGR, JOB
FROM SCOTT.EMP
START WITH ENAME = 'KING'
CONNECT BY PRIOR EMPNO = MGR;
from ptdept
start with deptid in ('698')
connect by prior deptid = parentdeptid