CONNECT BY子句

📄字数 1.3K

👁️阅读量加载中...

一、功能描述

层次查询（Hierarchical Query）用于处理具有父子层级关系的数据，生成带有层级关系的查询结果，使用CONNECT BY子句实现。

二、语法格式

三、参数解释

CONNECT BY bool_expr：层次结构的连接条件，描述父节点和子节点之间的关系。
START WITH bool_expr：层次结构的根节点，定义层次结构的起点。
NOCYCLE：防止循环引用，避免在层次结构中出现循环时无限递归。
KEEP bool_expr：在层次结构中进行聚合操作时，保留满足布尔表达式的特定行。
bool_expr：逻辑表达式，必须使用PRIOR运算符限定才能引用父行，如：
sql
```
... PRIOR expr = expr
or
... expr = PRIOR expr
```
1
2
3

四、相关伪列

LEVEL：表示当前节点在树状结构中的层级，LEVEL从1开始计数，随层级提升依次递增。
CONNECT_BY_ISLEAF：表示当前节点在树状结构中是否为叶子节点，是则为1，否则为0。
CONNECT_BY_ISCYCLE：表示当前节点是否会令层次关系中出现环，是则为1，否则为0。

五、示例

sql

SQL> CREATE TABLE tab_employees(emp_id INT, emp_name VARCHAR2(50), manager_id INT);

Execute successful.
Use time:43 ms.

SQL> INSERT INTO tab_employees VALUES (1, 'CEO', NULL)(2, 'CTO', 1)(3, 'CFO', 1)(4, 'Dev1', 2)(5, 'Dev2', 2)(6, 'Accountant', 3);

Total 6 records effected.
Use time:2 ms.

-- 查询层级关系及相关伪列
SQL> SELECT LEVEL as level, CONNECT_BY_ISLEAF as isleaf, CONNECT_BY_ISCYCLE as iscycle, emp_id, emp_name, manager_id
     FROM tab_employees START WITH manager_id IS NULL CONNECT BY PRIOR emp_id = manager_id;

+-------+--------+---------+--------+------------+------------+
| LEVEL | ISLEAF | ISCYCLE | EMP_ID |  EMP_NAME  | MANAGER_ID |
+-------+--------+---------+--------+------------+------------+
| 1     | 0      | 0       | 1      | CEO        | <NULL>     |
| 2     | 0      | 0       | 2      | CTO        | 1          |
| 3     | 1      | 0       | 4      | Dev1       | 2          |
| 3     | 1      | 0       | 5      | Dev2       | 2          |
| 2     | 0      | 0       | 3      | CFO        | 1          |
| 3     | 1      | 0       | 6      | Accountant | 3          |
+-------+--------+---------+--------+------------+------------+

(6 rows)
Use time:1 ms.

-- 调整CONNECT BY条件，从子节点向上查找根节点
SQL> SELECT LEVEL as level, CONNECT_BY_ISLEAF as isleaf, CONNECT_BY_ISCYCLE as iscycle, emp_id, emp_name, manager_id
     FROM tab_employees START WITH emp_id = 6 CONNECT BY PRIOR manager_id = emp_id;

+-------+--------+---------+--------+------------+------------+
| LEVEL | ISLEAF | ISCYCLE | EMP_ID |  EMP_NAME  | MANAGER_ID |
+-------+--------+---------+--------+------------+------------+
| 1     | 0      | 0       | 6      | Accountant | 3          |
| 2     | 0      | 0       | 3      | CFO        | 1          |
| 3     | 1      | 0       | 1      | CEO        | <NULL>     |
+-------+--------+---------+--------+------------+------------+

(3 rows)
Use time:0 ms.

CONNECT BY子句 ​

一、功能描述 ​

二、语法格式 ​

三、参数解释 ​

四、相关伪列 ​