层次查询

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

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

• 组织架构中组长和组员之间的关系

• 企业中上下级部门之间的关系

• Web 网页中页面跳转的关系

使用限制及注意事项

如果层次查询中包含了 FOR UPDATE 子句，那么不支持以下的使用场景：

• 如果子查询使用了 DISTINCT 关键字或者聚合，这种情况下将不能和 FOR UPDATE 一起使用。
• 任何包含公共表达式（Common Table Expressions，CTE）的情景都不支持，即带有 WITH ... AS ... 子句的 SELECT 查询不能与 FOR UPDATE 同时使用。更多 WITH ... AS ... 子句的信息，请参见 WITH CLAUSE。

为了保证数据库的正常操作及性能优化，请避免在上述提及的两种场景中使用 FOR UPDATE 子句。

语法

简单层次查询的 SELECT 语法如下：

SELECT [LEVEL], column, expr... FROM table [WHERE condition]
[ 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 ... ]
[FOR UPDATE [OF column] [ {NOWAIT | WAIT integer | SKIP LOCKED } ] ]

有关 SELECT 语法的详细信息，请参见 SIMPLE SELECT。

参数解释

执行流程

使用和实现层次查询最关键是要理解其执行流程，一般的层次查询执行流程如下：

1. 执行 FROM 后面的 SCAN 或 JOIN 操作。

2. 根据 START WITH 和 CONNECT BY 的内容生成层次关系结果。

   对于步骤 2 中生成层次关系的流程可以理解为：

   1. 根据 START WITH 中的表达式得到根行（Root Rows）。

   2. 根据 CONNECT BY 中的表达式选择每个根行（Root Rows）的子行（Child Rows）。

3. 将步骤 2 中生成的子行（Child Rows）作为新的根行（Root Rows）进一步生成子行（Child Rows），循环执行直到没有新行生成。

4. 按照常规查询执行流程执行剩下的子句（例如 WHERE、GROUP、ORDER BY......）。

示例

1. 创建表 emp，并向表中的 emp_id、position 和 mgr_id 列插入数据。

   CREATE TABLE emp(emp_id INT,position VARCHAR(50),mgr_id INT);
   INSERT INTO emp VALUES (1,'全球经理',NULL);
   INSERT INTO emp VALUES (2,'欧洲区经理',1);
   INSERT INTO emp VALUES (3,'亚太区经理',1);
   INSERT INTO emp VALUES (4,'美洲区经理',1);
   INSERT INTO emp VALUES (5,'意大利区经理',2);
   INSERT INTO emp VALUES (6,'法国区经理',2);
   INSERT INTO emp VALUES (7,'中国区经理',3);
   INSERT INTO emp VALUES (8,'韩国区经理',3);
   INSERT INTO emp VALUES (9,'日本区经理',3);
   INSERT INTO emp VALUES (10,'美国区经理',4);
   INSERT INTO emp VALUES (11,'加拿大区经理',4);
   INSERT INTO emp VALUES (12,'北京区经理',7);
   

   以上示例表中的列 position 具有清晰的层次关系。树状结构如下：

   

2. 执行以下语句将结果按照层次结构展示出来。

   SELECT emp_id, mgr_id, position, level
   FROM emp
   START WITH mgr_id IS NULL
       CONNECT BY PRIOR emp_id = mgr_id;
   

   返回结果如下：

   +--------+--------+--------------------+-------+
   | EMP_ID | MGR_ID | POSITION           | LEVEL |
   +--------+--------+--------------------+-------+
   |      1 |   NULL | 全球经理           |     1 |
   |      2 |      1 | 欧洲区经理         |     2 |
   |      5 |      2 | 意大利区经理       |     3 |
   |      6 |      2 | 法国区经理         |     3 |
   |      3 |      1 | 亚太区经理         |     2 |
   |      7 |      3 | 中国区经理         |     3 |
   |     12 |      7 | 北京区经理         |     4 |
   |      8 |      3 | 韩国区经理         |     3 |
   |      9 |      3 | 日本区经理         |     3 |
   |      4 |      1 | 美洲区经理         |     2 |
   |     10 |      4 | 美国区经理         |     3 |
   |     11 |      4 | 加拿大区经理       |     3 |
   +--------+--------+--------------------+-------+
   12 rows in set
   

3. 仅查询 "亚太区" 的层次结构，并且使用 FOR UPDATE 子句锁定查询结果。

   SELECT emp_id, mgr_id, position, LEVEL
   FROM emp
   START WITH position = '亚太区经理'
       CONNECT BY PRIOR emp_id = mgr_id
   FOR UPDATE;
   

   返回结果如下：

   +--------+--------+-----------------+-------+
   | EMP_ID | MGR_ID | POSITION        | LEVEL |
   +--------+--------+-----------------+-------+
   |      3 |      1 | 亚太区经理      |     1 |
   |      7 |      3 | 中国区经理      |     2 |
   |     12 |      7 | 北京区经理      |     3 |
   |      8 |      3 | 韩国区经理      |     2 |
   |      9 |      3 | 日本区经理      |     2 |
   +--------+--------+-----------------+-------+
   5 rows in set
   

相关文档

• SIMPLE SELECT
• WITH CLAUSE
• 锁定查询结果 SELECT FOR UPDATE