层次查询

    层次查询（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

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

    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. +--------+--------+-----------------+-------+