SQL语句——13、层次查询

emp表中的职员职位都是有上下级关系的

总裁king
|
/ \
经理jones 经理blake
| |
管理scott 职员james
|
职员adams

当表有层次结构时,通过使用层次查询可以更直观的显示数据结果和层次关系。

语法:

select [level],column,expr... from table
[where condition]
start with condition
connect by [prior column1 = column2 | column1 = prior column2];

伪列 level 用于返回层次查询的层次(1:根行 2:第2级行 3:第3级行...)
start with 用于指定层关系，即此查询的根行。决定了爬树的起点
connect by 用于指定父行和子行的关系。当定义父行和子行的关系时,必须使用prior关键字。决定了爬树的方向:
prior 用于指定哪个是父级列

SQL> select lpad('-',LEVEL,'-')||ename ename from emp start with (ename='ADAMS') connect by PRIOR mgr= empno;

ENAME
---------------
-ADAMS
--SCOTT
---JONES
----KING

SCOTT@ora10g>

SQL> select lpad('-',LEVEL,'-')||ename ename from emp start with (ename='JONES') connect by mgr= PRIOR empno;

ENAME
---------------
-JONES
--SCOTT
---ADAMS
--FORD
---SMITH

剪枝方法

剪枝条件出现在where子句，剪一个节点

select level,lpad(ename,length(ename)+level*2-2,' ') ename
from emp
where ename!='BLAKE'
start with ename='KING'
connect by prior empno=mgr;

剪枝条件出现在connect by prior子句，剪一个分支

select level,lpad(ename,length(ename)+level*2-2,' ') ename
from emp
start with ename='KING'
connect by prior empno=mgr
and ename!='BLAKE';

把问题简化下，比如有如下树形结构的数据：

pid id value

-----------------------

null 1000 10

1000 1010 10

1010 1011 10

1010 1012 10

现在想要得到如下的结果：

pid id sum_value

-----------------------

null 1000 40

1000 1010 30

1010 1011 10

1010 1012 10

也就是说,每个节点的sum_value都是它本身的value以及它的所有下级节点的value之和（而且并不知道有多少层）。比如：

id为1000的sum_value就是1000、1010、1011、1012的value值的合计，结果为40；

id为1010的sum_value就是1010、1011、1012的value值的合计，结果为30；

id为1011和1012是叶子节点，所以它们的sum_value就是它们自己的value的值，也就是10。

在oracle中怎么写SQL语句？

select tt.pid,tt.id,(
         select sum(t.value) from tree_test t
           start with id=tt.id
           connect by prior id = pid) sum_value
  from tree_test tt

循环打印数组

level伪列和connect by level一起可以作为循环使用。如：

SELECT LEVEL FROM DUAL
CONNECT BY LEVEL <= 5

LEVEL
--------
1
2
3
4
5