全部产品
云市场

层次查询

更新时间:2019-09-18 13:14:39

层次查询

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

  • 组织架构中leader和member之间的关系

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

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

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,prior是一元操作符,与一元的+和-具有相同的优先级。

    • NOCYCLE:当指定该关键字时,即使返回结果中有循环仍旧可以返回,并可以通过CONNECT_BY_ISCYCLE虚拟列来指明哪里出现循环;否则,出现循环会给客户端报错。

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

执行流程

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

  1. 执行From后面的Scan或Join操作

  2. 根据Start With和Connect By的内容生成层次关系结果

  3. 按照常规Query执行流程执行剩下的Clause(例如where、group、order by。。。)

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

  1. 根据Start With中的Expression得到root rows。

  2. 根据Connect By中的Expression选择每个root row的child rows。

  3. 将2中生成的child rows作为新的root rows进一步生成child rows,周而复始直到没有新row生成。

举例

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

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

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

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

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

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

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