类别：: 查询语法

CONNECT BY¶

将表与自身联接以处理表中的分层数据。FROM 子句的 CONNECT BY 分子句会迭代以处理数据。

例如，您可以创建一个显示“零件分解”的查询，以递归方式列出组件和该组件的子组件。

CONNECT BY 的 Snowflake 语法大多与 Oracle 语法兼容。

另请参阅：: WITH

语法¶

CONNECT BY 语句的一般形式类似于以下形式（允许顺序有某些变化，但没有显示出来）：

SELECT <column_list> [ , <level_expression> ]
  FROM <data_source>
    START WITH <predicate>
    CONNECT BY [ PRIOR ] <col1_identifier> = [ PRIOR ] <col2_identifier>
           [ , [ PRIOR ] <col3_identifier> = [ PRIOR ] <col4_identifier> ]
           ...
  ...

Copy

column_list

这通常遵循 SELECT 语句的投影子句的规则。

level_expression

CONNECT BY 查询允许某些伪列。其中一个伪列是 LEVEL，它指示层次结构的当前级别（其中级别 1 表示层次结构的顶层）。查询的投影子句可以将 LEVEL 用作列。

data_source

数据源通常是一个表，但也可以是其他类似表的数据源，如视图、UDTF 等。

predicate

谓词是选择层次结构的第一个“级别”（例如，公司总裁或零件分解中的顶级组件）的表达式。谓词应类似于 WHERE 子句，但没有关键字 WHERE。

有关谓词示例，请参阅示例部分（本主题内容）。

colN_identifier

CONNECT BY 子句应包含一个或多个类似于联接中使用的表达式。具体而言，表的“当前”级别的列应引用表的“先前”（更高）级别的列。

例如，在经理/员工层次结构中，该子句可能类似于：

... CONNECT BY manager_ID = PRIOR employee_ID ...

Copy

关键字 PRIOR 表示该值应从先前（更高/父）级别获取。

在本例中，当前员工的 manager_ID 应与先前级别的 employee_ID 相匹配。

CONNECT BY 子句可以包含多个这样的表达式，例如：

... CONNECT BY y = PRIOR x AND b = PRIOR a ...

Copy

类似于以下内容的每个表达式都应该只出现一次关键字 PRIOR：

CONNECT BY <col_1_identifier> = <col_2_identifier>

Copy

关键字 PRIOR 可能在 = 符号的左侧或右侧。例如：

CONNECT BY <col_1_identifier> = PRIOR <col_2_identifier>

Copy

或

CONNECT BY PRIOR <col_1_identifier> = <col_2_identifier>

Copy

使用说明¶

CONNECT BY 子句总是将一个表联接到自身，而不是另一个表。
投影子句中的某些变体是有效的。虽然语法显示 level_expression 出现在 column_list 之后，但级别表达式可以按任何顺序出现。
关键字 PRIOR 在每个 CONNECT BY 表达式中应该只出现一次。PRIOR 可以出现在表达式的左侧或右侧，但不能同时出现在两侧。
带有 CONNECT BY 的查询还可能包含以下一项或两项：
- WHERE 子句中的筛选器。
- JOINs （可能在 FROM 子句或 WHERE 子句中）。
评估顺序为：
1. JOINs（无论是在 WHERE 子句还是 FROM 子句中指定）。
2. CONNECT BY
3. 筛选器（ JOIN 筛选器除外）。
例如， WHERE 子句中的筛选器在 CONNECT BY 之后处理。
CONNECT BY 的 Snowflake 实现主要与 Oracle 实现兼容；但是，Snowflake 不支持：
- NOCYCLE
- CONNECT_BY_ISCYCLE
- CONNECT_BY_ISLEAF

与 CONNECT BY 子句一起使用时，Snowflake 支持函数 SYS_CONNECT_BY_PATH。SYS_CONNECT_BY_PATH 返回一个字符串，其中包含从根到当前元素的路径。下面的示例部分包含一个示例。

与 CONNECT BY 子句一起使用时，Snowflake 支持 CONNECT_BY_ROOT 运算符。CONNECT_BY_ROOT 运算符允许当前级别使用来自层次结构根级别的信息，即使根级别不是当前级别的直接父级也是如此。下面的示例部分包含一个示例。
CONNECT BY 子句可以根据需要迭代多次来处理数据。错误地构造查询可能会导致无限循环。在这些情况下，查询将继续运行，直到查询成功、查询超时（例如，超过 STATEMENT_TIMEOUT_IN_SECONDS 参数指定的秒数）或取消查询。

有关无限循环如何发生以及如何避免此问题的指南的信息，请参阅对递归 CTE 进行故障排除。

示例¶

此示例使用 CONNECT BY 在员工信息表中显示管理层次结构。表格和数据如下图所示：

CREATE OR REPLACE TABLE employees (title VARCHAR, employee_ID INTEGER, manager_ID INTEGER);

Copy

INSERT INTO employees (title, employee_ID, manager_ID) VALUES
    ('President', 1, NULL),  -- The President has no manager.
        ('Vice President Engineering', 10, 1),
            ('Programmer', 100, 10),
            ('QA Engineer', 101, 10),
        ('Vice President HR', 20, 1),
            ('Health Insurance Analyst', 200, 20);

Copy

查询和输出如下所示：

SELECT employee_ID, manager_ID, title
  FROM employees
    START WITH title = 'President'
    CONNECT BY
      manager_ID = PRIOR employee_id
  ORDER BY employee_ID;
+-------------+------------+----------------------------+
| EMPLOYEE_ID | MANAGER_ID | TITLE                      |
|-------------+------------+----------------------------|
|           1 |       NULL | President                  |
|          10 |          1 | Vice President Engineering |
|          20 |          1 | Vice President HR          |
|         100 |         10 | Programmer                 |
|         101 |         10 | QA Engineer                |
|         200 |         20 | Health Insurance Analyst   |
+-------------+------------+----------------------------+

Copy

此示例使用 SYS_CONNECT_BY_PATH 函数显示从总裁到当前员工的层次结构：

SELECT SYS_CONNECT_BY_PATH(title, ' -> '), employee_ID, manager_ID, title
  FROM employees
    START WITH title = 'President'
    CONNECT BY
      manager_ID = PRIOR employee_id
  ORDER BY employee_ID;
+----------------------------------------------------------------+-------------+------------+----------------------------+
| SYS_CONNECT_BY_PATH(TITLE, ' -> ')                             | EMPLOYEE_ID | MANAGER_ID | TITLE                      |
|----------------------------------------------------------------+-------------+------------+----------------------------|
|  -> President                                                  |           1 |       NULL | President                  |
|  -> President -> Vice President Engineering                    |          10 |          1 | Vice President Engineering |
|  -> President -> Vice President HR                             |          20 |          1 | Vice President HR          |
|  -> President -> Vice President Engineering -> Programmer      |         100 |         10 | Programmer                 |
|  -> President -> Vice President Engineering -> QA Engineer     |         101 |         10 | QA Engineer                |
|  -> President -> Vice President HR -> Health Insurance Analyst |         200 |         20 | Health Insurance Analyst   |
+----------------------------------------------------------------+-------------+------------+----------------------------+

Copy

此示例使用 CONNECT_BY_ROOT 关键字在输出的每一行中显示层次结构顶层的信息：

SELECT 
employee_ID, manager_ID, title,
CONNECT_BY_ROOT title AS root_title
  FROM employees
    START WITH title = 'President'
    CONNECT BY
      manager_ID = PRIOR employee_id
  ORDER BY employee_ID;
+-------------+------------+----------------------------+------------+
| EMPLOYEE_ID | MANAGER_ID | TITLE                      | ROOT_TITLE |
|-------------+------------+----------------------------+------------|
|           1 |       NULL | President                  | President  |
|          10 |          1 | Vice President Engineering | President  |
|          20 |          1 | Vice President HR          | President  |
|         100 |         10 | Programmer                 | President  |
|         101 |         10 | QA Engineer                | President  |
|         200 |         20 | Health Insurance Analyst   | President  |
+-------------+------------+----------------------------+------------+

Copy

此示例使用 CONNECT BY 来显示“零件分解图”：

数据如下：

-- The components of a car.
CREATE TABLE components (
    description VARCHAR,
    quantity INTEGER,
    component_ID INTEGER,
    parent_component_ID INTEGER
    );

INSERT INTO components (description, quantity, component_ID, parent_component_ID) VALUES
    ('car', 1, 1, 0),
       ('wheel', 4, 11, 1),
          ('tire', 1, 111, 11),
          ('#112 bolt', 5, 112, 11),
          ('brake', 1, 113, 11),
             ('brake pad', 1, 1131, 113),
       ('engine', 1, 12, 1),
          ('piston', 4, 121, 12),
          ('cylinder block', 1, 122, 12),
          ('#112 bolt', 16, 112, 12)   -- Can use same type of bolt in multiple places
    ;

Copy

查询和输出如下：

SELECT
  description,
  quantity,
  component_id, 
  parent_component_ID,
  SYS_CONNECT_BY_PATH(component_ID, ' -> ') AS path
  FROM components
    START WITH component_ID = 1
    CONNECT BY 
      parent_component_ID = PRIOR component_ID
  ORDER BY path
  ;
+----------------+----------+--------------+---------------------+----------------------------+
| DESCRIPTION    | QUANTITY | COMPONENT_ID | PARENT_COMPONENT_ID | PATH                       |
|----------------+----------+--------------+---------------------+----------------------------|
| car            |        1 |            1 |                   0 |  -> 1                      |
| wheel          |        4 |           11 |                   1 |  -> 1 -> 11                |
| tire           |        1 |          111 |                  11 |  -> 1 -> 11 -> 111         |
| #112 bolt      |        5 |          112 |                  11 |  -> 1 -> 11 -> 112         |
| brake          |        1 |          113 |                  11 |  -> 1 -> 11 -> 113         |
| brake pad      |        1 |         1131 |                 113 |  -> 1 -> 11 -> 113 -> 1131 |
| engine         |        1 |           12 |                   1 |  -> 1 -> 12                |
| #112 bolt      |       16 |          112 |                  12 |  -> 1 -> 12 -> 112         |
| piston         |        4 |          121 |                  12 |  -> 1 -> 12 -> 121         |
| cylinder block |        1 |          122 |                  12 |  -> 1 -> 12 -> 122         |
+----------------+----------+--------------+---------------------+----------------------------+

Copy