Oracle兼容-语法-分层查询（Hierarchical Query）

GreatSQL支持分层查询（Hierarchical Query）。

1. 语法

  1. { CONNECT BY [ NOCYCLE ] condition [ START WITH condition ]
  2. | START WITH condition CONNECT BY [ NOCYCLE ] condition
  3. }

2. 定义和用法

    1. 分层查询简介

为了查找父行的子行，需要计算父行的 CONNECT BY 条件的 PRIOR 表达式和表中每一行的另一个表达式。条件为真的行是父项的子项。CONNECT BY 条件可以包含其他条件以进一步过滤查询选择的行。

如果 CONNECT BY 条件导致层次结构中出现循环，则会返回错误。如果一行既是另一行的父项（或祖父项或直系祖先）又是子项（或孙项或直系后代），则会导致循环。

    1. 对分层查询的处理方式如下：
    • 1.) 如果存在连接，首先会被评估，无论连接是在FROM子句中还是在WHERE子句中指定。
    • 2.) CONNECT BY条件被评估。
    • 3.) 任何剩余的WHERE子句谓语都被评估。
    1. 对于分成查询的处理顺序为：
    • 1.) 选择层次结构的根行——那些满足 START WITH 条件的行。
    • 2.) 选择每个根行的子行。每个子行都必须满足与根行之一相关的 CONNECT BY 条件。
    • 3.) 择连续几代的子行。 选择在步骤 2 中返回的行的子项，然后是这些子项的子项，依此类推。
    • 4.) 如果查询包含没有连接的 WHERE 子句，那么会从层次结构中删除所有不满足 WHERE 子句条件的行。
    • 5.) 根据先序排列信息返回结果

示例：

分层查询Hierarchical Query

3. Oracle兼容说明

  • 1、查询表输出结果的排序和Oracle可能不一致

由于在遍历查询表数据方式与Oracle不一致，导致 CONNECT BY 单一层次内数据排序结果不一致。

  1. -- 初始化测试表及数据
  2. greatsql> CREATE TABLE student(
  3. id INT NOT NULL,
  4. name VARCHAR2(20) NOT NULL,
  5. grade INT NOT NULL
  6. );
  8. -- 案例1：测试数据顺序写入
  9. -- 顺序插入四条记录（id值顺序：1,2,3,4）
  10. greatsql> INSERT INTO student VALUES (1, 'John', -1);
  11. greatsql> INSERT INTO student VALUES (2, 'Paul', 1);
  12. greatsql> INSERT INTO student VALUES (3, 'Nancy', 1);
  13. greatsql> INSERT INTO student VALUES (4, 'Sarah', 2);
  15. greatsql> SELECT * FROM student;
  16. +------+-------+-------+
  17. | id   | name  | grade |
  18. +------+-------+-------+
  19. |    1 | John  |    -1 |
  20. |    2 | Paul  |     1 |
  21. |    3 | Nancy |     1 |
  22. |    4 | Sarah |     2 |
  23. +------+-------+-------+
  25. -- 此种情况下，GreatSQL和Oracle查询结果数据顺序一致
  26. greatsql> SELECT id, name, grade, LEVEL FROM student CONNECT BY PRIOR id = grade;
  27. +------+-------+-------+-------+
  28. | id   | name  | grade | LEVEL |
  29. +------+-------+-------+-------+
  30. |    1 | John  |    -1 |     1 |
  31. |    2 | Paul  |     1 |     2 |
  32. |    4 | Sarah |     2 |     3 |
  33. |    3 | Nancy |     1 |     2 |
  34. |    2 | Paul  |     1 |     1 |
  35. |    4 | Sarah |     2 |     2 |
  36. |    3 | Nancy |     1 |     1 |
  37. |    4 | Sarah |     2 |     1 |
  38. +------+-------+-------+-------+
  39. 8 rows in set (0.00 sec)
  41. -- 在Oracle中查询得到的结果如下，二者一致（建表和写入数据过程略过）
  42. SQL> SELECT id, name, grade, LEVEL FROM student CONNECT BY PRIOR id = grade;
  44.         ID NAME                      GRADE      LEVEL
  45. ---------- -------------------- ---------- ----------
  46.          1 John                         -1          1
  47.          2 Paul                          1          2
  48.          4 Sarah                         2          3
  49.          3 Nancy                         1          2
  50.          2 Paul                          1          1
  51.          4 Sarah                         2          2
  52.          3 Nancy                         1          1
  53.          4 Sarah                         2          1
  55. 8 rows selected.
  57. -- 案例2：测试数据乱序写入
  58. -- 清空表，调整数据写入顺序（id值顺序：3,4,1,2）
  59. greatsql> TRUNCATE TABLE student;
  61. greatsql> INSERT INTO student VALUES (3, 'Nancy', 1);
  62. greatsql> INSERT INTO student VALUES (4, 'Sarah', 2);
  63. greatsql> INSERT INTO student VALUES (1, 'John', -1);
  64. greatsql> INSERT INTO student VALUES (2, 'Paul', 1);
  66. greatsql> SELECT * FROM student;
  67. +------+-------+-------+
  68. | id   | name  | grade |
  69. +------+-------+-------+
  70. |    3 | Nancy |     1 |
  71. |    4 | Sarah |     2 |
  72. |    1 | John  |    -1 |
  73. |    2 | Paul  |     1 |
  74. +------+-------+-------+
  75. 4 rows in set (0.00 sec)
  77. -- 此种情况下，GreatSQL和Oracle查询结果数据顺序不一致
  78. greatsql> SELECT id, name, grade, LEVEL FROM student CONNECT BY PRIOR id = grade;
  79. +------+-------+-------+-------+
  80. | id   | name  | grade | LEVEL |
  81. +------+-------+-------+-------+
  82. |    3 | Nancy |     1 |     1 |
  83. |    4 | Sarah |     2 |     1 |
  84. |    1 | John  |    -1 |     1 |
  85. |    3 | Nancy |     1 |     2 |
  86. |    2 | Paul  |     1 |     2 |
  87. |    4 | Sarah |     2 |     3 |
  88. |    2 | Paul  |     1 |     1 |
  89. |    4 | Sarah |     2 |     2 |
  90. +------+-------+-------+-------+
  91. 8 rows in set (0.00 sec)
  93. -- 在Oracle中查询得到的结果如下，二者不一致（建表和写入数据过程略过）
  94. SQL> SELECT id, name, grade, LEVEL FROM student CONNECT BY PRIOR id = grade;
  96.         ID NAME                      GRADE      LEVEL
  97. ---------- -------------------- ---------- ----------
  98.          1 John                         -1          1
  99.          3 Nancy                         1          2
  100.          2 Paul                          1          2
  101.          4 Sarah                         2          3
  102.          3 Nancy                         1          1
  103.          2 Paul                          1          1
  104.          4 Sarah                         2          2
  105.          4 Sarah                         2          1
  107. 8 rows selected.

可以看到，由于第一个层级查询结果排序不一致，导致分层查询的结果顺序不一致。

  • 2、与 LISTAGG 结合使用输出组合顺序时，LISTAGG 的实现依赖 ORDER BY，因此在内部查询的时候会根据 GROUP BY 进行排序。

  • 3、在 ORACLE 模式下，SYSDATE 的行为与Oralce一致，在 DEFAULT 模式下，循环查询可能导致错误。

  • 4、与上一条类似，在自定义PACKAGE与FUNCTION中可能存在定义包含 DETERMINISTIC 的情况，也可能存在导致循环检查失效问题。

  • 5、当 CONNECT BY 中存在永真的条件时，不管是否使用了 PRIOR 或 CONNECT_BY_ROOT 的条件，会报告死循环错误。

  • 6、数据表中可能含有特殊的数据会产生死循环，或者单次遍历的子节点非常多，会根据 cte_max_recursion_depth 选项限制迭代数据大小。

  • 7、与 ROWNUM 伪列结合使用的时候，ROWNUM 在 WHERE 中作为查询条件结果会产生差异，如下例所示：

与Oracle区别：ROWNUM 的值会根据在 CONNECT BY 排序后的结果保存，而不重新计算。

  • 示例1：
  1. greatsql> SELECT LEVEL, ROWNUM FROM DUAL WHERE ROWNUM < 3 AND LEVEL = 2 CONNECT BY LEVEL <= 10;
  2. +-------+--------+
  3. | LEVEL | rownum |
  4. +-------+--------+
  5. |     2 |      2 |
  6. +-------+--------+
  8. -- 在Oracle中的执行结果如下
  9. SQL> SELECT LEVEL, ROWNUM FROM DUAL WHERE ROWNUM < 3 AND LEVEL = 2 CONNECT BY LEVEL <= 10;
  11.      LEVEL     ROWNUM
  12. ---------- ----------
  13.          2          1
  • 示例2
  1. greatsql> SELECT LEVEL, SYS_CONNECT_BY_PATH(ROWNUM, '->') FROM DUAL WHERE
  2.  ROWNUM < 3 AND LEVEL = 2
  3.  CONNECT BY LEVEL <= 10;
  4. +-------+-----------------------------------+
  5. | LEVEL | SYS_CONNECT_BY_PATH(ROWNUM, '->') |
  6. +-------+-----------------------------------+
  7. |     2 | ->1->2                            |
  8. +-------+-----------------------------------+
  10. -- 在Oracle中的执行结果如下
  11.      LEVEL   SYS_CONNECT_BY_PATH(ROWNUM,'->')
  12. ----------  -------------------------------------
  13.          2                                 ->1->1
  • 8、在GreatSQL中，支持在 CONNECT BY 条件中含有 BLOB 列，而Oracle不支持。

3. 分层查询伪列

分层查询伪列（Hierarchical Query Pseudocolumns）

分层查询伪列仅在分层查询中有效，包括以下三个伪列。

3.1 分层查询伪列 CONNECT_BY_ISCYCLE

表示在分层查询中，当前数据是否会导致形成循环。即根据层次关系，当前层数据是否存在其叶子节点也是其父节点。该列只有在同时指定 NOCYCLE 关键字时才有意义，当前数据会导致形成环则结果为1，否则为0。

3.2 分层查询伪列 CONNECT_BY_ISLEAF

表示在分层查询中，当前数据是否是分层查询形成的树结构中的叶子节点。若是叶子节点值为1，否则为0。此信息指示是否可以进一步扩展给定行以显示更多层次结构。

3.3 分层查询伪列 LEVEL

表示在分层查询中，形成的树结构中的当前层数。该列值一直从1开始，即 START WITH 对应的数据的层数一直为1，之后子孙节点的LEVEL值依次递增。

下图显示了倒置树的节点及其LEVEL值：

Pseudocolumn LEVEL

4. 分层查询操作符

GreatSQL提供如下两个标识符，可用于指定层次关系中的某个节点属性。

4.1 分层查询操作符 PRIOR

在分层查询中，CONNECT BY 条件中的一个表达式必须由 PRIOR 运算符限定。可以有多个 PRIOR 条件，但只有一个条件需要 PRIOR 运算符。PRIOR 操作符之后的参数将作为分层查询中的父节点。PRIOR 计算层次查询中当前行的父行的紧跟在后面的表达式。

PRIOR 使用列中父行的值。

PRIOR 最常用于使用相等运算符比较列值，PRIOR 关键字可以位于运算符的任一侧。

理论上，CONNECT BY 子句中可以使用等号 = 以外的运算符，但是其他运算符创建的条件可能会导致可能的组合出现无限循环。

4.2 分层查询操作符 CONNECT_BY_ROOT

CONNECT_BY_ROOT 是一元运算符，仅在分层查询中有效。当使用此运算符限定列时，将使用根行中的数据返回列值。

注意：

  1. 只支持基础数据类型，不支持JSON、GEO等特殊的数据类型。

  2. 参数不可以是虚拟列、层次查询函数、操作符、伪列及子查询。

  3. 两个操作符 PRIOR 和 CONNECT_BY_ROOT 都不能在 START WITH 子句中使用。

5. 分层查询函数 SYS_CONNECT_BY_PATH()

5.1 语法

  1. SYS_CONNECT_BY_PATH(column_name, DELIMITER)

其中 DELIMITER 表示分隔符。该函数将获取从根节点到当前节点的路径上所有节点名为column_name的值，中间用DELIMITER进行分隔开。SYS_CONNECT_BY_PATH()不能在 CONNECT BY、START WITH 和 GROUP BY 等几个子句中使用。

5.2 定义和用法

SYS_CONNECT_BY_PATH() 函数主要用于 树查询(层次查询) 以及 多列转行。

SYS_CONNECT_BY_PATH() 仅在分层查询中有效，它返回列值从根到节点的路径，对于 CONNECT BY 条件返回的每一行，列值由 char 分隔。

含有伪列的计算，并且存在 WHERE 过滤条件，ROWNUM 的值，根据在 CONNECT BY 排序后的结果将会保存，而不是重新计算，例如：

  • GreatSQL运行结果：
  1. greatsql> SELECT LEVEL, SYS_CONNECT_BY_PATH(ROWNUM, '->') FROM DUAL WHERE ROWNUM < 3 AND LEVEL=2 CONNECT BY LEVEL<=10;
  2. +-------+--------+
  3. | LEVEL | ROWNUM |
  4. +-------+--------+
  5. |     2 | ->1->2 |
  6. +-------+--------+
  • Oracle运行结果：
  1. SQL> SELECT LEVEL, SYS_CONNECT_BY_PATH(ROWNUM, '->') FROM DUAL WHERE ROWNUM < 3 AND LEVEL=2 CONNECT BY LEVEL<=10;
  3.      LEVEL    SYS_CONNECT_BY_PATH(ROWNUM,'->')
  4. ----------   ----------------------------------
  5.          2                               ->1->1

6. 示例

初始化测试表及数据。

  1. greatsql> CREATE TABLE student(
  2. id INT NOT NULL,
  3. name VARCHAR2(20) NOT NULL,
  4. grade INT NOT NULL
  5. );
  7. greatsql> INSERT INTO student VALUES (1, 'John', -1), (2, 'Paul', 1), (3, 'Nancy', 1), (4, 'Sarah', 2);

几个示例。

  1. greatsql> SELECT id, name, grade, LEVEL FROM student CONNECT BY PRIOR id = grade;
  2. +------+-------+-------+-------+
  3. | id   | name  | grade | LEVEL |
  4. +------+-------+-------+-------+
  5. |    1 | John  |    -1 |     1 |
  6. |    2 | Paul  |     1 |     2 |
  7. |    4 | Sarah |     2 |     3 |
  8. |    3 | Nancy |     1 |     2 |
  9. |    2 | Paul  |     1 |     1 |
  10. |    4 | Sarah |     2 |     2 |
  11. |    3 | Nancy |     1 |     1 |
  12. |    4 | Sarah |     2 |     1 |
  13. +------+-------+-------+-------+
  15. greatsql> SELECT id, name, grade, PRIOR name FROM student CONNECT BY PRIOR id = grade;
  16. +------+-------+-------+------------+
  17. | id   | name  | grade | PRIOR name |
  18. +------+-------+-------+------------+
  19. |    1 | John  |    -1 | NULL       |
  20. |    2 | Paul  |     1 | John       |
  21. |    4 | Sarah |     2 | Paul       |
  22. |    3 | Nancy |     1 | John       |
  23. |    2 | Paul  |     1 | NULL       |
  24. |    4 | Sarah |     2 | Paul       |
  25. |    3 | Nancy |     1 | NULL       |
  26. |    4 | Sarah |     2 | NULL       |
  27. +------+-------+-------+------------+
  28. 8 rows in set (0.00 sec)
  30. greatsql> SELECT id, CONNECT_BY_ROOT name FROM student CONNECT BY PRIOR id = grade;
  31. +------+----------------------+
  32. | id   | CONNECT_BY_ROOT name |
  33. +------+----------------------+
  34. |    1 | John                 |
  35. |    2 | John                 |
  36. |    4 | John                 |
  37. |    3 | John                 |
  38. |    2 | Paul                 |
  39. |    4 | Paul                 |
  40. |    3 | Nancy                |
  41. |    4 | Sarah                |
  42. +------+----------------------+
  43. 8 rows in set (0.01 sec)
  45. -- 修改当前时间戳
  46. greatsql> SET TIMESTAMP = 1;
  48. greatsql> SELECT id, grade, NOW() FROM student CONNECT BY NOCYCLE PRIOR id = (grade + 1);
  49. +------+-------+---------------------+
  50. | id   | grade | NOW()               |
  51. +------+-------+---------------------+
  52. |    1 |    -1 | 1970-01-01 03:00:01 |
  53. |    2 |     1 | 1970-01-01 03:00:01 |
  54. |    3 |     1 | 1970-01-01 03:00:01 |
  55. |    4 |     2 | 1970-01-01 03:00:01 |
  56. |    3 |     1 | 1970-01-01 03:00:01 |
  57. |    4 |     2 | 1970-01-01 03:00:01 |
  58. |    4 |     2 | 1970-01-01 03:00:01 |
  59. +------+-------+---------------------+
  60. 7 rows in set (0.00 sec)
  62. greatsql> SELECT id, name, grade, CONNECT_BY_ISCYCLE FROM student CONNECT BY NOCYCLE PRIOR id = (grade + 1);
  63. +------+-------+-------+--------------------+
  64. | id   | name  | grade | CONNECT_BY_ISCYCLE |
  65. +------+-------+-------+--------------------+
  66. |    1 | John  |    -1 |                  0 |
  67. |    2 | Paul  |     1 |                  1 |
  68. |    3 | Nancy |     1 |                  0 |
  69. |    4 | Sarah |     2 |                  0 |
  70. |    3 | Nancy |     1 |                  0 |
  71. |    4 | Sarah |     2 |                  0 |
  72. |    4 | Sarah |     2 |                  0 |
  73. +------+-------+-------+--------------------+
  74. 7 rows in set (0.01 sec)
  76. greatsql> SELECT id, name, grade, CONNECT_BY_ISLEAF FROM student CONNECT BY NOCYCLE PRIOR id = (grade + 1);
  77. +------+-------+-------+-------------------+
  78. | id   | name  | grade | CONNECT_BY_ISLEAF |
  79. +------+-------+-------+-------------------+
  80. |    1 | John  |    -1 |                 1 |
  81. |    2 | Paul  |     1 |                 0 |
  82. |    3 | Nancy |     1 |                 0 |
  83. |    4 | Sarah |     2 |                 1 |
  84. |    3 | Nancy |     1 |                 0 |
  85. |    4 | Sarah |     2 |                 1 |
  86. |    4 | Sarah |     2 |                 1 |
  87. +------+-------+-------+-------------------+
  88. 7 rows in set (0.01 sec)
  90. greatsql> SELECT id, grade, LEVEL, SYS_CONNECT_BY_PATH(id ,'->'), PRIOR name, CONNECT_BY_ROOT id FROM student CONNECT BY PRIOR id = grade;
  91. +------+-------+-------+-------------------------------+------------+--------------------+
  92. | id   | grade | LEVEL | SYS_CONNECT_BY_PATH(id ,'->') | PRIOR name | CONNECT_BY_ROOT id |
  93. +------+-------+-------+-------------------------------+------------+--------------------+
  94. |    1 |    -1 |     1 | ->1                           | NULL       |                  1 |
  95. |    2 |     1 |     2 | ->1->2                        | John       |                  1 |
  96. |    4 |     2 |     3 | ->1->2->4                     | Paul       |                  1 |
  97. |    3 |     1 |     2 | ->1->3                        | John       |                  1 |
  98. |    2 |     1 |     1 | ->2                           | NULL       |                  2 |
  99. |    4 |     2 |     2 | ->2->4                        | Paul       |                  2 |
  100. |    3 |     1 |     1 | ->3                           | NULL       |                  3 |
  101. |    4 |     2 |     1 | ->4                           | NULL       |                  4 |
  102. +------+-------+-------+-------------------------------+------------+--------------------+
  103. 8 rows in set (0.00 sec)

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx