用 SQL 查询/构建树型(层次)数据

 

什么是层次/树型是什么概念？

If a table contains hierarchical data, then you can select rows in a hierarchical. (单击查看内容出处) 

注：在Oracle里称为hierarchical queries，而为了方便理解，把它译为层次/树型查询。

作用 & 特征

通常用于查询整个层次/树型关系的数据。如：公司的架构体系里面，表里存储了每一层职位的信息，需要全部查出来；（树的形式） 一些顺序型的记录，像：A群由上海搬到北京，之后又搬到广州，不久又搬到南京…。（单链表形式）

 

凡是在表里存在以上形式的数据，都可以根据一个点，它所有关联的数据通过一条SELECT语句一次过查询出来。

示例

网上有太多的层次/树型结构的例子，而且在Oracle官方文档里也有很好的例子(单击查看文档例子)。因此本文从数据结构的角度去说明一下具体的用法，不涉及任何业务知识。

先看一张关系图:

至于上图的树型，我们通常说成：1 是根节点；4的父节点是2，子节点有5、6；而像3、5、6…这些没有子节点的节点称为叶结点。（不同的描述有不同的说法，在文章继续之前先统一一下说法）

用表把上面的图存起来。

SQL代码 CREATE TABLE t_tree(fnum NUMBER, fparent NUMBER);     INSERT INTO t_tree(fnum, fparent) VALUES(1, null);      INSERT INTO t_tree(fnum, fparent) VALUES(2, 1);     INSERT INTO t_tree(fnum, fparent) VALUES(3, 2);      INSERT INTO t_tree(fnum, fparent) VALUES(4, 2);      INSERT INTO t_tree(fnum, fparent) VALUES(5, 4);     INSERT INTO t_tree(fnum, fparent) VALUES(6, 4);     INSERT INTO t_tree(fnum, fparent) VALUES(7, 1);      INSERT INTO t_tree(fnum, fparent) VALUES(8, 7);     INSERT INTO t_tree(fnum, fparent) VALUES(9, 1);    INSERT INTO t_tree(fnum, fparent) VALUES(10, 9);     INSERT INTO t_tree(fnum, fparent) VALUES(11, 10);     INSERT INTO t_tree(fnum, fparent) VALUES(12, 9);     COMMIT;

根据需求写SQL:

查出 1 下面的所有成员：（顺推）

SQL代码 SELECT fparent  -- 父节点             ,fnum    -- 子节点           ,LEVEL AS n_level  -- 第几层(逻辑)           ,sys_connect_by_path(fnum, '/') AS path -- 整个路径           ,connect_by_isLeaf AS "isLeaf" -- 是否为叶节点(1 - 是；0 - 否)       FROM t_tree      START WITH fparent = 1     CONNECT BY PRIOR fnum = fparent     结果 FPARENT       FNUM    N_LEVEL PATH             isLeaf    ------- ---------- ---------- ------------   ----------          1          2          1 /2                 0          2          3          2 /2/3               1          2          4          2 /2/4               0          4          5          3 /2/4/5             1          4          6          3 /2/4/6             1          1          7          1 /7                 0          7          8          2 /7/8               1          1          9          1 /9                 0          9         10          2 /9/10              0         10         11          3 /9/10/11           1          9         12          2 /9/12              1       rows selected

 

查出 8 上面的所有成员：（逆推）

SQL代码 SELECT fparent -- 父节点              ,fnum   -- 子节点              ,LEVEL AS n_level -- -- 第几层(逻辑)              ,sys_connect_by_path(fnum, '/') AS path -- 整个路径              ,connect_by_isLeaf AS "isLeaf" -- 是否为叶节点(1 - 是；0 - 否)         FROM t_tree        START WITH fnum = 8       CONNECT BY fnum = PRIOR fparent       结果 FPARENT       FNUM    N_LEVEL      PATH           isLeaf       --------- ---------- ---------- ------------- ----------          7          8        1            /8               0          1          7        2            /8/7             0                     1        3            /8/7/1           1

 

查出所有叶（Leaf）结点，没有子节点的节点：

SQL代码 SELECT *      FROM t_tree tr     WHERE 0 = (SELECT COUNT(1)                  FROM t_tree                 WHERE fparent = tr.fnum)     ORDER BY fnum     结果 FNUM    FPARENT    ---- ----------       3          2       5          4       6          4       8          7      11         10      12          9

 

其它说明

调优

内部不断根据子、父节点来查询本表。数据量大的话，给表添加子、父节点两个字段的索引，会有很明显的快速效果。

 

根节点

Oracle 提供了可以快速找到根节点的操作符，如下SQL：

SQL代码 SELECT fparent  -- 父节点             ,fnum    -- 子节点           ,LEVEL AS n_level  -- 第几层(逻辑)           ,sys_connect_by_path(fnum, '/') AS path -- 整个路径           ,connect_by_root fparent AS root_top -- 根节点       FROM t_tree      START WITH fparent = 9     CONNECT BY PRIOR fnum = fparent     结果 FPARENT       FNUM    N_LEVEL PATH               ROOT_TOP    ------- ---------- ---------- ----------------- ----------          9         10          1 /10                  9         10         11          2 /10/11               9          9         12          1 /12                  9

 

如果指定一个节点开始查的话，ROOT就是指定的那个节点，直接用 START WITH 后面那个值就行。不过如果没有指定一个节点，表里有多棵树，这个就很有用了。

 

循环关系

在记录里有循环关系，树型有根和子节点，而没有循环的关系。这里简单说明一下，循环是怎回事吧。如添加一条这样的记录：

SQL代码 INSERT INTO t_tree(fnum, fparent) VALUES(7, 8);

 

在 1-7-8 这条路径制造一条循环记录。之后从8开始逆推到上一层（会报错）：

SQL代码 SQL> SELECT fparent -- 父节点      2         ,fnum   -- 子节点      3         ,LEVEL AS n_level -- -- 第几层(逻辑)      4         ,sys_connect_by_path(fnum, '/') AS path -- 整个路径      5         ,connect_by_isLeaf AS "isLeaf" -- 是否为叶节点(1 - 是；0 - 否)      6    FROM t_tree      7   START WITH fnum = 8      8   CONNECT BY fnum = PRIOR fparent      9  /       SELECT fparent -- 父节点           ,fnum   -- 子节点           ,LEVEL AS n_level -- -- 第几层(逻辑)           ,sys_connect_by_path(fnum, '/') AS path -- 整个路径           ,connect_by_isLeaf AS "isLeaf" -- 是否为叶节点(1 - 是；0 - 否)      FROM t_tree     START WITH fnum = 8     CONNECT BY fnum = PRIOR fparent       ORA-01436: 用户数据中的 CONNECT BY 循环       SQL>

 

这里需要用到一个循环处理，如下：

SQL代码 SQL> SELECT fparent -- 父节点      2         ,fnum   -- 子节点      3         ,LEVEL AS n_level -- -- 第几层(逻辑)      4         ,sys_connect_by_path(fnum, '/') AS path -- 整个路径      5         ,connect_by_isLeaf AS "isLeaf" -- 是否为叶节点(1 - 是；0 - 否)      6         ,connect_by_isCycle AS "isCycle" -- 是否为循环节点(1 - 是；0 - 否)      7    FROM t_tree      8   START WITH fnum = 8      9   CONNECT BY NOCYCLE fnum = PRIOR fparent     10  /          FPARENT       FNUM    N_LEVEL PATH                 isLeaf    isCycle    ---------- ---------- ---------- ------------------ ---------- ----------             7          8          1 /8                     0          0             1          7          2 /8/7                   0          0                        1          3 /8/7/1                 1          0             8          7          2 /8/7                   1          1