1. 分层查询

Oracle 中的connect by 语法用于分层查询，它根据构成父子关系的数据按层次顺序返回结果集的行。Oracle 中分层查询的语法如下：

SELECT select_list FROM table_expression [ WHERE ...]

[ START WITH start_expression ]

CONNECT BY { PRIOR parent_expr = child_expr |

child_expr = PRIOR parent_expr }

CONNECT BY 子句用于描述父级和子级关系的纽带。CONNECT BY 子句必须由使用等号 (=) 表达式组成。此外，等号 (=) 两边的表达式之一必须前面带有关键字PRIOR ，用于表示父级表达式。

分层查询本质是一种递归查询，用第一层查询的结果递归出后一层。在 PG 中可以使用 WITH RECURSIVE 语法实现相同的功能。普通的 WITH 子句可以实现 CTE 的功能，加上 RECURSIVE 关键字可以进一步在 WITH 内引用自己的输出实现递归。WITH RECURSIVE 语法由几个主要组成部分：

WITH RECURSIVE cte_name AS (

CTE_query_definition -- non-recursive term

UNION [ALL]

CTE_query definion -- recursive term

) SELECT * FROM cte_name;

递归 CTE 有三个要素:

非递归项: 非递归项是一个查询定义，返回 CTE 的基础结果集。

递归项: 递归项是一个或多个 CTE 查询定义，它使用 UNION 或 UNION ALL 运算符与非递归项结合在一起。递归项引用 CTE 名称本身。

终止检查: 当前一次迭代没有返回任何行时，递归停止。

2. with recursive 语法实现分层查询

Oracle 的分层查询的用法比较多，下面看一下Oracle 分层查询不同用法所对应的WITH RECURSIVE 的转换。

2.1. 指定分层查询中的起始条件

Oracle 可以在分层查询中指定START WITH 子句确定要作为根节点的行，这些行是要应用递归查询的起始行。PG 可以在WITH RECURSIVE 中指定where 子句实现相同的效果。

2.2. 显示分层查询中的层级数

Oracle 的分层查询可以通过指定LEVEL 关键字显示每行所在分层查询中的层级数。对于结果集中的每一行，LEVEL 返回一个非零整数值，指出由此行表示的节点在层次结构中的深度。根节点的LEVEL 为1 。根节点的直接子级的LEVEL 为2 ，依此类推。PG 中可以在WITH RECURSIVE 中指定LEVEL 列实现同样的效果。

2.3. 显示分层查询中的层级结构

Oracle 分层查询中使用SYS_CONNECT_BY_PATH ，可以显示分成查询的层级结构。其语法格式如下：

SYS_CONNECT_BY_PATH (column, delimiter)

它返回column 列从根节点到当前节点的所有列值，列值由delimiter 分隔。

PG 的WITH RECURSIVE 同样可以实现该效果，每次递归的时候追加上次返回的列值，同时添加分隔符。

2.4. 显示当前行的父节点