--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.
点我的名字,看我的博客。里面有你想要的。 ORACLE 各种连接方式 总结。
scott@TBWORA> with a as(select p_id, id from tb start with p_id=1 connect by prior id=p_id) 2 select (case when p_id<>1 then 1 else p_id end) as p_id, id from a order by id; P_ID ID ---------- ---------- 1 2 1 3 1 4 1 5
SELECT e.id,(select h.p_id FROM 你的表 h where CONNECT_BY_ISLEAF=1 START WITH h.id=e.id CONNECT BY PRIOR h.p_id = h.id) p_id FROM 你的表 e order by p_id 以上可以实现你举得这个例子
有 所有一级节点的 上级节点都是‘ZZZ’
改成这样就行了。 SELECT e.id,(select h.id FROM 你的表 h where h.p_id='ZZZ' START WITH h.id=e.id CONNECT BY PRIOR h.p_id = h.id) p_id FROM 你的表 e order by p_id
--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.
ORACLE 各种连接方式
总结。
2 select (case when p_id<>1 then 1 else p_id end) as p_id, id from a order by id; P_ID ID
---------- ----------
1 2
1 3
1 4
1 5
有没有能查询所有子节点和一级根节点的对应关系 不用限定某一个子节点 比如:
表结构 id p_id(上级id)
2 1
3 1
4 2
5 3
6 7
8 7
查询后的结果:
1 2
1 3
1 4
1 5
7 6
7 8
id p_id
1 空值
where CONNECT_BY_ISLEAF=1
START WITH h.id=e.id
CONNECT BY PRIOR h.p_id = h.id) p_id
FROM 你的表 e
order by p_id
以上可以实现你举得这个例子
SELECT e.id,(select h.id FROM 你的表 h
where h.p_id='ZZZ'
START WITH h.id=e.id
CONNECT BY PRIOR h.p_id = h.id) p_id
FROM 你的表 e
order by p_id