实现层次查询，你们猜用了多久

ywz888

发布于 2022-10-10 16:14

浏览

0收藏

实现层次查询，你们猜用了多久-鸿蒙开发者社区

介绍

层次查询（Hierarchical Query）是一种具有特殊功能的查询语句，通过它能够将Hierarchical Data按照层次关系展示出来。

Hierarchical Data是指Relation中的数据之间具有层次关系。这种关系在现实生活中十分常见，例如：

组织架构中leader和member之间的关系
企业中上下级部门之间的关系
Web网页中，页面跳转的关系

Hierarchical Data实际上较适合存储在Hierarchical Data Mode 数据库中，在这类数据库中数据就是按照其层次关系（树形结构）进行存储。比较典型的实现是IBM Information Management System 。而对于当前主流的关系型数据库，处理Hierarchical Data就需要用Hierarchical Query来达到目的。当前关系数据库通常使用两种典型的方法来实现Hierarchical Query的功能，分别是：

Common Table Expression(CTE)

CET是一个查询的临时结果，可以被看做生成Derived Table的一种方式（类似view，subquery）。使用CET即可以达到层次查询的功能，例如查询emp表中employee和manager的关系，SQL表示方式如下：

WITH ReportingTree (EmpID, EmpName, MgrID, Lvl) 
AS ( 
    SELECT *, 0 as Lvl FROM emp WHERE MgrId IS NULL 
    UNION ALL 
    SELECT emp.EmpID, emp.EmpName, emp.MgrId, ReportingTree.Lvl+1     FROM emp INNER JOIN ReportingTree on emp.MgrID = ReportingTree.EmpID 
) 
SELECT * FROM ReportingTree

Connect By

Connect By是Oracle 10g中引入的语法，它在语法上更加简明和清晰，可以使用户更轻松的写出层次查询语句，并且Oracle为层次查询提供了一些

虚拟列和

函数来更好的展示层次查询的结果。对于上面的例子通过Connect By语法的实现为：

SELECT EmpID, EmpName, MgrId, Level FROM emp START WITH MgrId IS NULL CONNECT BY PRIOR EmpId = MgrId

OceanBase1.0中的层次查询使用Connect By语法实现，并提供了相关虚拟列和函数。

使用

Connect By语法形态

SELECT select_list
FROM table_expression
[ WHERE ... ]
[ START WITH start_expression ]
CONNECT BY [NOCYCLE] { PRIOR child_expr = parent_expr | parent_expr = PRIOR child_expr }
[ ORDER SIBLINGS BY ...]
[ GROUP BY ... ]
[ HAVING ... ]
[ ORDER BY ... ]

上面描述了一条具有层次查询功能的Query形态，其中一部分是我们已经熟知的Clause，另一部分是与层次查询密切相关的Clause：

START WITH : 指明层次查询中的root row(s)
CONNECT BY : 指明如何来确定父子关系，这里通常使用等值表达式，但其他表达式同样支持。

PRIOR操作符 : 表示参数中的column来自于parent row。
NOCYCLE：当指定该关键字时，即使返回结果中有循环仍旧可以返回，并可以通过CONNECT_BY_ISCYCLE虚拟列来指明哪里出现循环；否则，出现循环会给客户端报错。

ORDER SIBLINGS BY：指定
同一个层级rows之间的排列顺序。

执行流程

使用和实现层次查询最关键是要理解其执行流程，如果一个Query为层次查询，执行流程可以描述为：

执行From后面的Scan或Join操作
根据Start With和Connect By的内容生成层次关系结果
按照常规Query执行流程执行剩下的Clause（例如where、group、order by。。。）

对于2中生成层次关系的流程可以描述为：

根据Start With中的Expression得到root rows。
根据Connect By中的Expression选择每个root row的child rows。
将2中生成的child rows作为新的root rows进一步生成child rows，周而复始直到没有新row生成。

举例

通过一个例子来更直观的展示层次查询的使用，表中内容如下所示，为了易于理解将职位名称按照地理位置进行描述：

OceanBase (root@oceanbase)> select * from emp;
+--------+--------------------+--------+
| emp_id | position           | mgr_id |
+--------+--------------------+--------+
|      1 | 全球经理           |   NULL |
|      2 | 欧洲区经理         |      1 |
|      3 | 亚太区经理         |      1 |
|      4 | 美洲区经理         |      1 |
|      5 | 意大利区经理       |      2 |
|      6 | 法国区经理         |      2 |
|      7 | 中国区经理         |      3 |
|      8 | 韩国区经理         |      3 |
|      9 | 日本区经理         |      3 |
|     10 | 美国区经理         |      4 |
|     11 | 加拿大区经理       |      4 |
|     12 | 北京区经理         |      7 |
+--------+--------------------+--------+
12 rows in set (0.00 sec)

通过上面的内容可以看见position具有清晰层次关系。其关系通过树形结构可以更好的表现出来：

实现层次查询，你们猜用了多久-鸿蒙开发者社区

层次查询的结果如下所示，是按照层次结构将结果展示出来：

OceanBase (root@oceanbase)> select emp_id, mgr_id, position from emp start with mgr_id is NULL connect by prior emp_id = mgr_id;
+--------+--------+--------------------+
| emp_id | mgr_id | position           |
+--------+--------+--------------------+
|      1 |   NULL | 全球经理           |
|      4 |      1 | 美洲区经理         |
|     11 |      4 | 加拿大区经理       |
|     10 |      4 | 美国区经理         |
|      3 |      1 | 亚太区经理         |
|      9 |      3 | 日本区经理         |
|      8 |      3 | 韩国区经理         |
|      7 |      3 | 中国区经理         |
|     12 |      7 | 北京区经理         |
|      2 |      1 | 欧洲区经理         |
|      6 |      2 | 法国区经理         |
|      5 |      2 | 意大利区经理       |
+--------+--------+--------------------+

如果仅查询“亚太区”的层次结构可以使用

OceanBase (root@oceanbase)> select emp_id, mgr_id, position from emp start with position = "亚太区经理" connect by prior emp_id = mgr_id;
+--------+--------+-----------------+| emp_id | mgr_id | position        |
+--------+--------+-----------------+|      3 |      1 | 亚太区经理      |
|      9 |      3 | 日本去经理      |
|      8 |      3 | 韩国区经理      |
|      7 |      3 | 中国区经理      ||     12 |      7 | 北京区经理      |
+--------+--------+-----------------+