表结构 id p_id(上级id)
2 1
3 1
4 2
5 3
6 7
我现在想得 p_id 为1的 跟所有子节点左关联的数据 如下图
1 2
1 3
1 4
1 5求助 在oracle10g sql该怎么写。
2 1
3 1
4 2
5 3
6 7
我现在想得 p_id 为1的 跟所有子节点左关联的数据 如下图
1 2
1 3
1 4
1 5求助 在oracle10g sql该怎么写。
调试欢乐多
--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