层次查询(Hierarchical Query)是一种具有特殊功能的查询语句,通过它能够将分层数据按照层次关系展示出来。分层数据是指关系表中的数据之间具有层次关系。这种关系在现实生活中十分常见,例如:

  • 组织架构中组长和组员之间的关系。
  • 企业中上下级部门之间的关系。
  • Web 网页中,页面跳转的关系。

语法

  1. SELECT [level], column, expr... FROM table [WHERE condition] [ START WITH start_expression ]
  2. CONNECT BY [NOCYCLE] { PRIOR child_expr = parent_expr | parent_expr = PRIOR child_expr }
  3. [ ORDER SIBLINGS BY …] [ GROUP BY ] [ HAVING ] [ ORDER BY ]

参数

参数说明
LEVEL节点的层次,是伪列,表示等级。由查询的起点开始算起为 1,依次类推。

CONNECT_BY_ISLEAF

当前数据行是否是层次关系中的叶子节点,是伪列,0表示不是,1表示是。

CONNECT_BY_ISCYCLE

当前数据行是否在循环中,是伪列,0表示不是,1表示是。

CONNECT_BY_ROOT运算符

CONNECT_BY_ROOT是一元运算符,表示参数中的列来自于层次查询的根节点,与一元的 + 和 - 具有相同的优先级。

condition条件。
CONNECT BY指明如何来确定父子关系,这里通常使用等值表达式,但其他表达式同样支持。
START WITH指明层次查询中的根行(Root row)。
PRIOR 运算符PRIOR 是一元运算符,表示参数中的列来自于父行(Parent row),与一元的 + 和 - 具有相同的优先级。
NOCYCLE当指定该关键字时,即使返回结果中有循环仍旧可以返回,并可以通过 CONNECT_BY_ISCYCLE 虚拟列来指明哪里出现循环;否则,出现循环会给客户端报错。
ORDER SIBLINGS BY指定同一个层级行之间的排列顺序。

执行流程

使用和实现层次查询最关键是要理解其执行流程,层次查询执行流程:

  1. 执行 FROM 后面的 SCANJOIN 操作;
  2. 根据 START WITHCONNECT BY 的内容生成层次关系结果;
  3. 按照常规查询执行流程执行剩下的子句(例如 WHEREGROUPORDER BY……)
    对于 2 中生成层次关系的流程可以理解为:
  4. 根据 START WITH 中的表达式得到根行(Root rows)。
  5. 根据 CONNECT BY 中的表达式 选择每个根行(Root rows)的子行(Child rows)。
  6. 将 2 中生成的子行(Child rows)作为新的根行(Root rows)进一步生成子行(Child rows),周而复始直到没有新行生成。

示例

展示层次查询的使用,向表 emp中的 emp_idpositionmgr_id 列插入数据。执行以下语句:

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

通过上面的内容可以看见列 position 具有清晰的层次关系。树状结构如下:

层次查询 - 图1

是按照层次结构将结果展示出来,执行以下语句:

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

查询结果如下:

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

如果仅查询“亚太区”的层次结构,执行以下语句:

  1. SELECT emp_id, mgr_id, position, level FROM emp START WITH position = '亚太区经理' CONNECT BY PRIOR emp_id = mgr_id;

查询结果如下:

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