层次查询

语法格式

SELECT { { column | LEVEL | CONNECT_BY_ISLEAF | CONNECT_BY_ISCYCLE | SYS_CONNECT_BY_PATH(col, separator) | CONNECT_BY_ROOT(col) } [, ...] }
 FROM table
START WITH condition
CONNECT BY [NOCYCLE] [PRIOR column1 = column2|column1 = prior column2]
[ORDER SIBLINGS BY expression] [ORDER BY expression]

参数说明

• LEVEL：伪列, 用于返回查询的层次。
• CONNECT_BY_ISLEAF：伪列，用于返回当前节点是否为叶子节点。
• CONNECT_BY_ISCYCLE ：伪列，用于返回当前节点是否与父行/子行成环。
• NOCYCLE：指定该选项时，遇到循环记录时停止递归。未指定该选项时，遇到循环记录时报错。
• SYS_CONNECT_BY_PATH(col, separator)：用于层次查询的函数，返回从根节点到当前行的连接路径。参数col为路径中显示的列的名称，separator为连接符。
• CONNECT_BY_ROOT(col)：用于层次查询的函数，显示该节点最顶级的节点，col为输出列的名称
• START WITH：用于指定层次关系，即查询的根行。
• CONNECT BY：用来指定父行和子行的关系。
• PRIOR：用于指定哪一个是父级别。
• ORDER SIBLINGS BY：指定同一层级之间的排列顺序。

示例

--建表并插入数据。
gaussdb=# CREATE TABLE area (id INT,name VARCHAR(25),parent_id INT);
gaussdb=# INSERT INTO area VALUES (1,'中国',NULL);
gaussdb=# INSERT INTO area VALUES (2,'北京市',1);
gaussdb=# INSERT INTO area VALUES (3,'朝阳区',2);
gaussdb=# INSERT INTO area VALUES (4,'陕西省',1);
gaussdb=# INSERT INTO area VALUES (5,'西安市',4);
gaussdb=# INSERT INTO area VALUES (6,'雁塔区',5);
gaussdb=# INSERT INTO area VALUES (7,'未央区',5);

--层次查询。
gaussdb=# SELECT level,
    name,
    connect_by_isleaf,
    connect_by_iscycle,
    connect_by_root(name),
    sys_connect_by_path(name, '->')
FROM area START WITH (id = 1) CONNECT BY PRIOR id = parent_id;
 level |  name  | connect_by_isleaf | connect_by_iscycle | connect_by_root |      sys_connect_by_path       
-------+--------+-------------------+--------------------+-----------------+--------------------------------
     1 | 中国   |                 0 |                  0 | 中国            | ->中国
     2 | 陕西省 |                 0 |                  0 | 中国            | ->中国->陕西省
     3 | 西安市 |                 0 |                  0 | 中国            | ->中国->陕西省->西安市
     4 | 未央区 |                 1 |                  0 | 中国            | ->中国->陕西省->西安市->未央区
     4 | 雁塔区 |                 1 |                  0 | 中国            | ->中国->陕西省->西安市->雁塔区
     2 | 北京市 |                 0 |                  0 | 中国            | ->中国->北京市
     3 | 朝阳区 |                 1 |                  0 | 中国            | ->中国->北京市->朝阳区
(7 rows)

• 使用WHERE过滤掉一个节点（仅北京市）｡

  --使用WHERE过滤掉一个节点（仅北京市）｡
  gaussdb=# SELECT level, name FROM area 
      WHERE id <> 2 
      START WITH (id = 1) 
      CONNECT BY PRIOR id = parent_id;
   level |  name  
  -------+--------
       1 | 中国
       2 | 陕西省
       3 | 西安市
       4 | 未央区
       4 | 雁塔区
       3 | 朝阳区
  (6 rows)

• 层次查询遇到循环记录

  gaussdb=# INSERT INTO area VALUES (8,'江苏省',10);
  gaussdb=# INSERT INTO area VALUES (9,'南京市',8);
  gaussdb=# INSERT INTO area VALUES (10,'雨花台区',9);
  gaussdb=# SELECT level,
      name,
      connect_by_isleaf,
      connect_by_iscycle,
      connect_by_root(name),
      sys_connect_by_path(name, '->')
  FROM area START WITH (id = 8) CONNECT BY NOCYCLE PRIOR id = parent_id;
   level |   name   | connect_by_isleaf | connect_by_iscycle | connect_by_root |    sys_connect_by_path     
  -------+----------+-------------------+--------------------+-----------------+----------------------------
       1 | 江苏省   |                 0 |                  0 | 江苏省          | ->江苏省
       2 | 南京市   |                 0 |                  0 | 江苏省          | ->江苏省->南京市
       3 | 雨花台区 |                 1 |                  1 | 江苏省          | ->江苏省->南京市->雨花台区
  (3 rows)
  
  gaussdb=# DELETE FROM area WHERE id >= 8;

• 在CONNECT BY PRIOR子句中添加条件，过滤掉一个分支（北京市及其子地区）。

  gaussdb=# SELECT level, name FROM area 
      START WITH (id = 1) 
      CONNECT BY PRIOR id = parent_id AND id <> 2;
   level |  name  
  -------+--------
       1 | 中国
       2 | 陕西省
       3 | 西安市
       4 | 未央区
       4 | 雁塔区
  (5 rows)
  
  -- 删除。
  gaussdb=# DROP TABLE area;