层次查询

语法格式

SELECT [level], column FROM table
START WITH condition
CONNECT BY[PRIOR column1 = column2|column1 = prior column2]
[ORDER SIBLINGS BY] [GROUP BY]

参数说明

• level：伪列用于返回查询的层次。
• 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 FROM area 
    START WITH (id = 1) 
    CONNECT BY PRIOR id = parent_id;
 level |  name  
-------+--------
     1 | 中国
     2 | 陕西省
     3 | 西安市
     4 | 未央区
     4 | 雁塔区
     2 | 北京市
     3 | 朝阳区
(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)

• 在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;