用 SQL 查询/构建树型(层次)数据 |
您所在的位置：网站首页 › sql查询树状结构 › 用 SQL 查询/构建树型(层次)数据 |
用 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 |
【本文地址】
公司简介
联系我们
今日新闻 |
点击排行 |
|
推荐新闻 |
图片新闻 |
|
专题文章 |