具有递归 CTE 的 Oracle CONNECT BY 功能

Oracle 的 CONNECT BY 功能提供了许多有用且非常重要的功能，这些功能在使用 SQL 标准递归 CTE 时不是内置的。此示例使用 SQL Server 语法复制这些功能（为了完整性，添加了一些功能）。对于 Oracle 开发人员来说，在其他数据库的分层查询中发现许多缺少的功能是最有用的，但它也可以用来展示分层查询通常可以做些什么。

  WITH tbl AS (
       SELECT id, name, parent_id
         FROM mytable)
     , tbl_hierarchy AS (
       /* Anchor */
       SELECT 1 AS "LEVEL"
            --, 1 AS CONNECT_BY_ISROOT
            --, 0 AS CONNECT_BY_ISBRANCH
            , CASE WHEN t.id IN (SELECT parent_id FROM tbl) THEN 0 ELSE 1 END AS CONNECT_BY_ISLEAF
            , 0 AS CONNECT_BY_ISCYCLE
            , '/' + CAST(t.id   AS VARCHAR(MAX)) + '/' AS SYS_CONNECT_BY_PATH_id
            , '/' + CAST(t.name AS VARCHAR(MAX)) + '/' AS SYS_CONNECT_BY_PATH_name
            , t.id AS root_id
            , t.*
         FROM tbl t
        WHERE t.parent_id IS NULL                            -- START WITH parent_id IS NULL
       UNION ALL
       /* Recursive */
       SELECT th."LEVEL" + 1 AS "LEVEL"
            --, 0 AS CONNECT_BY_ISROOT
            --, CASE WHEN t.id IN (SELECT parent_id FROM tbl) THEN 1 ELSE 0 END AS CONNECT_BY_ISBRANCH
            , CASE WHEN t.id IN (SELECT parent_id FROM tbl) THEN 0 ELSE 1 END AS CONNECT_BY_ISLEAF
            , CASE WHEN th.SYS_CONNECT_BY_PATH_id LIKE '%/' + CAST(t.id AS VARCHAR(MAX)) + '/%' THEN 1 ELSE 0 END AS CONNECT_BY_ISCYCLE
            , th.SYS_CONNECT_BY_PATH_id   + CAST(t.id   AS VARCHAR(MAX)) + '/' AS SYS_CONNECT_BY_PATH_id
            , th.SYS_CONNECT_BY_PATH_name + CAST(t.name AS VARCHAR(MAX)) + '/' AS SYS_CONNECT_BY_PATH_name
            , th.root_id
            , t.*
         FROM tbl t
              JOIN tbl_hierarchy th ON (th.id = t.parent_id) -- CONNECT BY PRIOR id = parent_id
        WHERE th.CONNECT_BY_ISCYCLE = 0)                     -- NOCYCLE
SELECT th.*
     --, REPLICATE(' ', (th."LEVEL" - 1) * 3) + th.name AS tbl_hierarchy
  FROM tbl_hierarchy th
       JOIN tbl CONNECT_BY_ROOT ON (CONNECT_BY_ROOT.id = th.root_id)
 ORDER BY th.SYS_CONNECT_BY_PATH_name;                       -- ORDER SIBLINGS BY name

上面演示了 CONNECT BY 功能，并附有说明：

• 条款
  • CONNECT BY：指定定义层次结构的关系。
  • START WITH：指定根节点。
  • 订购 SIBLINGS BY：正确订购结果。
• 参数
  • NOCYCLE：检测到循环时停止处理分支。有效层次结构是有向非循环图，循环引用违反此构造。
• 运算符
  • PRIOR：从节点的父节点获取数据。
  • CONNECT_BY_ROOT：从节点的根获取数据。
• 伪列
  • LEVEL：表示节点与其根的距离。
  • CONNECT_BY_ISLEAF：表示没有子节点的节点。
  • CONNECT_BY_ISCYCLE：表示具有循环引用的节点。
• 功能
  • SYS_CONNECT_BY_PATH：返回从其根节点到该节点的路径的展平/连接表示。