CONNECT_BY_ROOT 是一元运算符,可用于限定列,以便根据当前行返回被视为根节点的行的列值。

一元运算符对单个操作数执行运算;就 CONNECT_BY_ROOT 而言,该操作数是 CONNECT_BY_ROOT 关键字后面的列名称。

在 SELECT 列表的上下文中,CONNECT_BY_ROOT 运算符如下所示。

SELECT [... ,] CONNECT_BY_ROOT column [, ...]
  FROM table_expression ...

以下是一些需要注意的有关 CONNECT_BY_ROOT 运算符的要点。

  • CONNECT_BY_ROOT 运算符可在 SELECT 列表、WHERE 子句、GROUP BY 子句、HAVING 子句、ORDER BY 子句和 ORDER SIBLINGS BY 子句中使用,只要 SELECT 命令用于分层查询。
  • CONNECT_BY_ROOT 运算符不能在分层查询的 CONNECTBY 子句或 START WITH 子句中使用。
  • 可将 CONNECT_BY_ROOT 应用于一个涉及列的表达式,但这样做时,该表达式必须用圆括号括起。

以下查询显示了如何使用 CONNECT_BY_ROOT 运算符根据以员工 BLAKE、CLARK 和 JONES 开头的树,为结果集中列出的每个员工返回根节点的员工编号和员工名称。

SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr,
CONNECT_BY_ROOT empno "mgr empno",
CONNECT_BY_ROOT ename "mgr ename"
FROM emp
START WITH ename IN ('BLAKE','CLARK','JONES')
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename ASC;

您会看到,该查询的输出显示 mgr empno 和 mgr ename 列中的所有根节点都是 START WITH 子句中列出的 BLAKE、CLARK 或 JONES 中的一个员工。

 level | employee  | empno | mgr  | mgr empno | mgr ename
-------+-----------+-------+------+-----------+-----------
     1 | BLAKE     |  7698 | 7839 |      7698 | BLAKE
     2 |   ALLEN   |  7499 | 7698 |      7698 | BLAKE
     2 |   JAMES   |  7900 | 7698 |      7698 | BLAKE
     2 |   MARTIN  |  7654 | 7698 |      7698 | BLAKE
     2 |   TURNER  |  7844 | 7698 |      7698 | BLAKE
     2 |   WARD    |  7521 | 7698 |      7698 | BLAKE
     1 | CLARK     |  7782 | 7839 |      7782 | CLARK
     2 |   MILLER  |  7934 | 7782 |      7782 | CLARK
     1 | JONES     |  7566 | 7839 |      7566 | JONES
     2 |   FORD    |  7902 | 7566 |      7566 | JONES
     3 |     SMITH |  7369 | 7902 |      7566 | JONES
     2 |   SCOTT   |  7788 | 7566 |      7566 | JONES
     3 |     ADAMS |  7876 | 7788 |      7566 | JONES
(13 rows)

下面是一个类似的查询,但只生成一个以单个顶层级别员工开头的树,其中 mgr 列为 null。

SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr,
CONNECT_BY_ROOT empno "mgr empno",
CONNECT_BY_ROOT ename "mgr ename"
FROM emp START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename ASC;

在以下输出中,mgr empno 和 mgr ename 列中的所有根节点指示 KING 作为此特定查询的根。

 level |  employee   | empno | mgr  | mgr empno | mgr ename
-------+-------------+-------+------+-----------+-----------
     1 | KING        |  7839 |      |      7839 | KING
     2 |   BLAKE     |  7698 | 7839 |      7839 | KING
     3 |     ALLEN   |  7499 | 7698 |      7839 | KING
     3 |     JAMES   |  7900 | 7698 |      7839 | KING
     3 |     MARTIN  |  7654 | 7698 |      7839 | KING
     3 |     TURNER  |  7844 | 7698 |      7839 | KING
     3 |     WARD    |  7521 | 7698 |      7839 | KING
     2 |   CLARK     |  7782 | 7839 |      7839 | KING
     3 |     MILLER  |  7934 | 7782 |      7839 | KING
     2 |   JONES     |  7566 | 7839 |      7839 | KING
     3 |     FORD    |  7902 | 7566 |      7839 | KING
     4 |       SMITH |  7369 | 7902 |      7839 | KING
     3 |     SCOTT   |  7788 | 7566 |      7839 | KING
     4 |       ADAMS |  7876 | 7788 |      7839 | KING
(14 rows)

相比之下,以下示例省略了 START WITH 子句,从而生成十四个树。

SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr,
CONNECT_BY_ROOT empno "mgr empno",
CONNECT_BY_ROOT ename "mgr ename"
FROM emp
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename ASC;

下面是该查询的输出。每个节点作为根节点至少在 mgr empno 和 mgrename 列下出现一次,因为甚至叶节点也构成其自己树的顶层。

 level |  employee   | empno | mgr  | mgr empno | mgr ename
-------+-------------+-------+------+-----------+-----------
     1 | ADAMS       |  7876 | 7788 |      7876 | ADAMS
     1 | ALLEN       |  7499 | 7698 |      7499 | ALLEN
     1 | BLAKE       |  7698 | 7839 |      7698 | BLAKE
     2 |   ALLEN     |  7499 | 7698 |      7698 | BLAKE
     2 |   JAMES     |  7900 | 7698 |      7698 | BLAKE
     2 |   MARTIN    |  7654 | 7698 |      7698 | BLAKE
     2 |   TURNER    |  7844 | 7698 |      7698 | BLAKE
     2 |   WARD      |  7521 | 7698 |      7698 | BLAKE
     1 | CLARK       |  7782 | 7839 |      7782 | CLARK
     2 |   MILLER    |  7934 | 7782 |      7782 | CLARK
     1 | FORD        |  7902 | 7566 |      7902 | FORD
     2 |   SMITH     |  7369 | 7902 |      7902 | FORD
     1 | JAMES       |  7900 | 7698 |      7900 | JAMES
     1 | JONES       |  7566 | 7839 |      7566 | JONES
     2 |   FORD      |  7902 | 7566 |      7566 | JONES
     3 |     SMITH   |  7369 | 7902 |      7566 | JONES
     2 |   SCOTT     |  7788 | 7566 |      7566 | JONES
     3 |     ADAMS   |  7876 | 7788 |      7566 | JONES
     1 | KING        |  7839 |      |      7839 | KING
     2 |   BLAKE     |  7698 | 7839 |      7839 | KING
     3 |     ALLEN   |  7499 | 7698 |      7839 | KING
     3 |     JAMES   |  7900 | 7698 |      7839 | KING
     3 |     MARTIN  |  7654 | 7698 |      7839 | KING
     3 |     TURNER  |  7844 | 7698 |      7839 | KING
     3 |     WARD    |  7521 | 7698 |      7839 | KING
     2 |   CLARK     |  7782 | 7839 |      7839 | KING
     3 |     MILLER  |  7934 | 7782 |      7839 | KING
     2 |   JONES     |  7566 | 7839 |      7839 | KING
     3 |     FORD    |  7902 | 7566 |      7839 | KING
     4 |       SMITH |  7369 | 7902 |      7839 | KING
     3 |     SCOTT   |  7788 | 7566 |      7839 | KING
     4 |       ADAMS |  7876 | 7788 |      7839 | KING
     1 | MARTIN      |  7654 | 7698 |      7654 | MARTIN
     1 | MILLER      |  7934 | 7782 |      7934 | MILLER
     1 | SCOTT       |  7788 | 7566 |      7788 | SCOTT
     2 |   ADAMS     |  7876 | 7788 |      7788 | SCOTT
     1 | SMITH       |  7369 | 7902 |      7369 | SMITH
     1 | TURNER      |  7844 | 7698 |      7844 | TURNER
     1 | WARD        |  7521 | 7698 |      7521 | WARD
(39 rows)

下面阐释了 CONNECT_BY_ROOT 的一元运算符作用。如此示例中所示,当应用于不带圆括号的表达式时,CONNECT_BY_ROOT 运算符仅影响紧跟其后的 ename 一词。随后的 || ' manages ' || ename 串联不是 CONNECT_BY_ROOT 运算的一部分,因此,第二次出现的 ename 生成当前处理行的值,而第一次出现的 ename 生成根节点的值。

SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr,
CONNECT_BY_ROOT ename || ' manages ' || ename "top mgr/employee"
FROM emp
START WITH ename IN ('BLAKE','CLARK','JONES')
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename ASC;

下面是该查询的输出。您会看到,top mgr/employee 列下方生成的值。

 level | employee  | empno | mgr  |   top mgr/employee
-------+-----------+-------+------+----------------------
     1 | BLAKE     |  7698 | 7839 | BLAKE manages BLAKE
     2 |   ALLEN   |  7499 | 7698 | BLAKE manages ALLEN
     2 |   JAMES   |  7900 | 7698 | BLAKE manages JAMES
     2 |   MARTIN  |  7654 | 7698 | BLAKE manages MARTIN
     2 |   TURNER  |  7844 | 7698 | BLAKE manages TURNER
     2 |   WARD    |  7521 | 7698 | BLAKE manages WARD
     1 | CLARK     |  7782 | 7839 | CLARK manages CLARK
     2 |   MILLER  |  7934 | 7782 | CLARK manages MILLER
     1 | JONES     |  7566 | 7839 | JONES manages JONES
     2 |   FORD    |  7902 | 7566 | JONES manages FORD
     3 |     SMITH |  7369 | 7902 | JONES manages SMITH
     2 |   SCOTT   |  7788 | 7566 | JONES manages SCOTT
     3 |     ADAMS |  7876 | 7788 | JONES manages ADAMS
(13 rows)

以下示例将 CONNECT_BY_ROOT 运算符用于带圆括号的表达式。

SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr,
CONNECT_BY_ROOT ('Manager ' || ename || ' is emp # ' || empno)
"top mgr/empno"
FROM emp
START WITH ename IN ('BLAKE','CLARK','JONES')
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename ASC;

下面是该查询的输出。您会看到,ename 和 empno 的值均受 CONNECT_BY_ROOT 运算符的影响,因此从根节点返回值,如 top mgr/empno 列下方所示。

 level | employee  | empno | mgr  |        top mgr/empno
-------+-----------+-------+------+-----------------------------
     1 | BLAKE     |  7698 | 7839 | Manager BLAKE is emp # 7698
     2 |   ALLEN   |  7499 | 7698 | Manager BLAKE is emp # 7698
     2 |   JAMES   |  7900 | 7698 | Manager BLAKE is emp # 7698
     2 |   MARTIN  |  7654 | 7698 | Manager BLAKE is emp # 7698
     2 |   TURNER  |  7844 | 7698 | Manager BLAKE is emp # 7698
     2 |   WARD    |  7521 | 7698 | Manager BLAKE is emp # 7698
     1 | CLARK     |  7782 | 7839 | Manager CLARK is emp # 7782
     2 |   MILLER  |  7934 | 7782 | Manager CLARK is emp # 7782
     1 | JONES     |  7566 | 7839 | Manager JONES is emp # 7566
     2 |   FORD    |  7902 | 7566 | Manager JONES is emp # 7566
     3 |     SMITH |  7369 | 7902 | Manager JONES is emp # 7566
     2 |   SCOTT   |  7788 | 7566 | Manager JONES is emp # 7566
     3 |     ADAMS |  7876 | 7788 | Manager JONES is emp # 7566
(13 rows)