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.        顯示當前行的父節點

Oracle 分層查詢中使用CONNECT_BY_ROOT ，可以顯示每行的上層節點列。CONNECT_BY_ROOT 是一個一元運算子，用於指定某一列。