层次查询

语法格式

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过滤掉一个节点（仅北京市）｡
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)

-- 在SELECT查询列中添加PRIOR修饰id列，显示父行元组。
gaussdb=# SELECT level, PRIOR id as pid, parent_id, name FROM area 
    START WITH (id = 1) 
    CONNECT BY PRIOR id = parent_id;
 level | pid | parent_id |  name  
-------+-----+-----------+--------
     1 |     |           | 中国
     2 |   1 |         1 | 陕西省
     3 |   4 |         4 | 西安市
     4 |   5 |         5 | 未央区
     4 |   5 |         5 | 雁塔区
     2 |   1 |         1 | 北京市
     3 |   2 |         2 | 朝阳区
(7 rows)

-- 删除表。
gaussdb=# DROP TABLE area;