树结构吧,估计得用connect by 了。 提供些例子,自己参考: --1.Hierarchical Queries: START WITH and CONNECT BY PRIOR clauses --Hierarchical Queries --START WITH and CONNECT BY PRIOR clauses.SELECT employee_id, manager_id, first_name, last_name FROM employee_jh START WITH employee_id = 1 CONNECT BY PRIOR employee_id = manager_id;EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME ----------- ---------- ---------- ---------- 1 0 James Smith 2 1 Ron Johnson 3 2 Fred Hobbs 5 2 Rob Green 4 1 Susan Jones 6 4 Jane Brown 9 6 Henry Heyson 7 4 John Grey 8 7 Jean Blue 10 1 Kevin Black 11 10 Keith Long 12 10 Frank Howard 13 10 Doreen Penn13 rows selected.--2.Using a Subquery in a START WITH Clause SELECT LEVEL, LPAD(' ', 2 * LEVEL - 1) || first_name || ' ' || last_name AS employee FROM employee_jh START WITH employee_id = (SELECT employee_id FROM employee_jh WHERE first_name = 'Kevin' AND last_name = 'Black') CONNECT BY PRIOR employee_id = manager_id; LEVEL EMPLOYEE ---------- ------------------------- 1 Kevin Black 2 Keith Long 2 Frank Howard 2 Doreen Penn--3.Including Other Conditions in a Hierarchical Query SELECT LEVEL, LPAD(' ', 2 * LEVEL - 1) || first_name || ' ' || last_name AS employee, salary FROM employee_jh WHERE salary <= 50000 START WITH employee_id = 1 CONNECT BY PRIOR employee_id = manager_id; LEVEL EMPLOYEE SALARY ---------- ------------------------- ---------- 3 Rob Green 40000 3 Jane Brown 45000 4 Henry Heyson 30000 3 John Grey 30000 4 Jean Blue 29000 3 Keith Long 50000 3 Frank Howard 45000 3 Doreen Penn 470008 rows selected.
提供些例子,自己参考:
--1.Hierarchical Queries: START WITH and CONNECT BY PRIOR clauses
--Hierarchical Queries
--START WITH and CONNECT BY PRIOR clauses.SELECT employee_id, manager_id, first_name, last_name
FROM employee_jh
START WITH employee_id = 1
CONNECT BY PRIOR employee_id = manager_id;EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME
----------- ---------- ---------- ----------
1 0 James Smith
2 1 Ron Johnson
3 2 Fred Hobbs
5 2 Rob Green
4 1 Susan Jones
6 4 Jane Brown
9 6 Henry Heyson
7 4 John Grey
8 7 Jean Blue
10 1 Kevin Black
11 10 Keith Long
12 10 Frank Howard
13 10 Doreen Penn13 rows selected.--2.Using a Subquery in a START WITH Clause
SELECT LEVEL,
LPAD(' ', 2 * LEVEL - 1) || first_name || ' ' || last_name AS employee
FROM employee_jh
START WITH employee_id = (SELECT employee_id FROM employee_jh WHERE first_name = 'Kevin' AND last_name = 'Black')
CONNECT BY PRIOR employee_id = manager_id; LEVEL EMPLOYEE
---------- -------------------------
1 Kevin Black
2 Keith Long
2 Frank Howard
2 Doreen Penn--3.Including Other Conditions in a Hierarchical Query
SELECT LEVEL,
LPAD(' ', 2 * LEVEL - 1) || first_name || ' ' ||
last_name AS employee, salary
FROM employee_jh
WHERE salary <= 50000
START WITH employee_id = 1
CONNECT BY PRIOR employee_id = manager_id; LEVEL EMPLOYEE SALARY
---------- ------------------------- ----------
3 Rob Green 40000
3 Jane Brown 45000
4 Henry Heyson 30000
3 John Grey 30000
4 Jean Blue 29000
3 Keith Long 50000
3 Frank Howard 45000
3 Doreen Penn 470008 rows selected.