SQL 分层查询

1. 遍历树

分层查询目的是迅速找出表中父列–子列的隶属关系，在日常工作中我们经常查询机构表的上级机、下级机构；以及部门经理与员工的上下级树形关系。

树形表的遍历有两个方向:

top–down 自上而下

即父亲找儿子，一个父亲可能有几个儿子，一个儿子可能有几个孙子，遍历不能丢了儿子，顺序以左为先。

down–top 自底向上

即儿子找父亲，一个儿子只能有一个父亲，所以顺序应该是：孙子->儿子–>父亲–>爷爷。

2 分层查询语法

在ORACLE数据中经常使用CONNECT BY 和 START WITH 子句可以查询表中的树型结构关系。

语法如下：

SELECT ...
CONNECT BY {PRIOR 列名 1=列名 2|列名 1=PRIOR 列名 2}
[START WITH]

我们以EMP作为示例：

在emp表中empno 是子列（下级）,mgr 是父列（上级）

PRIOR 关键字就像一个箭头(“–>”)

(1)connect by prior empno = mgr
(2)connect by mgr = prior empno

两句语法等同，都是说 mgr（父）–> empno(子），因此树的检索方向是 top –> down。

connect by empno = prior mgr
connect by prior mgr = empno

两句语法等同，都是说 empno(子）–> mgr(父），因此树的检索方向是 down –> top。prior 关键字之后紧随的字段作为查询的方向起点。

START WITH子句为可选项，用来标识哪个节点作为查找树型结构的根节点。若该子句被省略，则表示所有满足查询条件的行作为根节点。

举例：

select empno,ename,mgr 
from emp
connect by prior empno=mgr
start with empno='7839'

select empno,ename,mgr
from emp
connect by empno=prior mgr
start with empno=7369

在自顶向下查询树结构时，不但可以从根节点开始，还可以定义任何节点为起始节点，以此开始向下查找。这样查找的结果就是以该节点为开始的结构树的一枝。

SELECT EMPNO,ENAME,MGR
FROM EMP
CONNECT BY PRIOR EMPNO=MGR
START WITH EMPNO=7566

START WITH 还可以指定多个根节点

SELECT EMPNO,ENAME,MGR
FROM EMP
CONNECT BY PRIOR EMPNO=MGR
START WITH ENAME IN ('FORD','BLAKE')

3 使用 LEVEL 伪列显示层级

在查询中，可以使用伪列 LEVEL 显示每行数据的有关层次。LEVEL 将返回树型结构中当前节点的层次。

SELECT 
LEVEL as "LEVEL",EMPNO,ENAME,MGR
FROM EMP
CONNECT BY PRIOR EMPNO=MGR
START WITH ENAME='KING'

只查看第3层的员工信息

select t1.* 
from (select level LNUM ,ename,mgr 
     from emp 
     connect by prior empno=mgr 
     start with ename='KING') t1 
where LNUM=3

4 节点和分子裁剪

在对树结构进行查询时，可以去掉表中的某些行，也可以剪掉树中的一个分支，使用 WHERE 子句来限定树型结构中的单个节点，以去掉树中的单个节点，但它却不影响其后代节点。

SELECT LPAD(LEVEL,LEVEL*3,' ') 
as "LEVEL",EMPNO,ENAME,MGR
FROM EMP
WHERE ENAME<>'SCOTT'
CONNECT BY PRIOR EMPNO=MGR
START WITH ENAME='KING'

SELECT LPAD(LEVEL,LEVEL*3,' ') 
as "LEVEL",EMPNO,ENAME,MGR
FROM EMP
CONNECT BY PRIOR EMPNO=MGR
AND ENAME!='SCOTT'
START WITH ENAME='KING'

在使用 SELECT 语句来报告树结构报表时应当注意，CONNECT BY 子句不能作用于出现在 WHERE 子句中的表连接。如果需要进行连接，可以先用树结构建立一个视图，再将这个视图与其他表连接，以完成所需要的查询。

原文始发于微信公众号（晓彬聊数据）：SQL 分层查询

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/264353.html