postgresql能不能進行遞歸查詢

小編給大家分享一下postgresql能不能進行遞歸查詢，相信大部分人都還不怎么了解，因此分享這邊文章給大家學習，希望大家閱讀完這篇文章后大所收獲，下面讓我們一起去學習方法吧！

PostgreSQL提供了WITH語句，允許你構造用于查詢的輔助語句。這些語句通常稱為公共表表達式或cte。cte類似于只在查詢執行期間存在的臨時表。

遞歸查詢是指遞歸CTE的查詢。遞歸查詢在很多情況下都很有用，比如查詢組織結構、物料清單等層次數據。

下面演示了遞歸CTE的語法：

WITH RECURSIVE cte_name(
    CTE_query_definition -- non-recursive term
    UNION [ALL]
    CTE_query definion  -- recursive term
) SELECT * FROM cte_name;

遞歸CTE有三個元素：

1.非遞歸項：非遞歸項是CTE查詢定義，它構成CTE結構的基本結果集。

2.遞歸項：遞歸項是使用UNION或UNION ALL操作符將一個或多個CTE查詢定義與非遞歸項連接起來。遞歸項引用CTE名稱本身。

3.終止檢查：當上一個迭代沒有返回任何行時，遞歸將停止。

PostgreSQL按以下順序執行遞歸CTE：

1.執行非遞歸項來創建基本結果集(R0)。

2.以Ri作為輸入執行遞歸項，返回結果集Ri+1作為輸出。

3.重復步驟2，直到返回一個空集。(終止檢查)

4.返回最終的結果集，它是一個并集，或者是所有結果集R0、R1、……Rn的并集。

我們將創建一個新表來演示PostgreSQL遞歸查詢。

CREATE TABLE employees (
   employee_id serial PRIMARY KEY,
   full_name VARCHAR NOT NULL,
   manager_id INT
);

員工表由三個列組成:employee_id、manager_id和全名。manager_id列指定employee的manager id。

下面的語句將示例數據插入employees表。

INSERT INTO employees (
   employee_id,
   full_name,
   manager_id
)
VALUES
   (1, 'Michael North', NULL),
   (2, 'Megan Berry', 1),
   (3, 'Sarah Berry', 1),
   (4, 'Zoe Black', 1),
   (5, 'Tim James', 1),
   (6, 'Bella Tucker', 2),
   (7, 'Ryan Metcalfe', 2),
   (8, 'Max Mills', 2),
   (9, 'Benjamin Glover', 2),
   (10, 'Carolyn Henderson', 3),
   (11, 'Nicola Kelly', 3),
   (12, 'Alexandra Climo', 3),
   (13, 'Dominic King', 3),
   (14, 'Leonard Gray', 4),
   (15, 'Eric Rampling', 4),
   (16, 'Piers Paige', 7),
   (17, 'Ryan Henderson', 7),
   (18, 'Frank Tucker', 8),
   (19, 'Nathan Ferguson', 8),
   (20, 'Kevin Rampling', 8);

下面的查詢返回id為2的經理的所有下屬。

WITH RECURSIVE subordinates AS (
   SELECT
      employee_id,
      manager_id,
      full_name
   FROM
      employees
   WHERE
      employee_id = 2
   UNION
      SELECT
         e.employee_id,
         e.manager_id,
         e.full_name
      FROM
         employees e
      INNER JOIN subordinates s ON s.employee_id = e.manager_id
) SELECT
   *
FROM
   subordinates;

上面sql的工作原理：

1.遞歸CTE subordinates定義了一個非遞歸項和一個遞歸項。

2.非遞歸項返回基本結果集R0，即id為2的員工。

employee_id | manager_id | full_name
------------+------------+-------------
       2 |       1 | Megan Berry

 遞歸項返回員工id 2的直接下屬。這是employee表和subordinates CTE之間連接的結果。遞歸項的第一次迭代返回以下結果集:

employee_id | manager_id | full_name
------------+------------+-----------------
       6 |       2 | Bella Tucker
       7 |       2 | Ryan Metcalfe
       8 |       2 | Max Mills
       9 |       2 | Benjamin Glover

 PostgreSQL重復執行遞歸項。遞歸成員的第二次迭代使用上述步驟的結果集作為輸入值，返回該結果集：

employee_id | manager_id | full_name
------------+------------+-----------------
      16 |       7 | Piers Paige
      17 |       7 | Ryan Henderson
      18 |       8 | Frank Tucker
      19 |       8 | Nathan Ferguson
      20 |       8 | Kevin Rampling

第三次迭代返回一個空的結果集，因為沒有員工向id為16、17、18、19和20的員工。

PostgreSQL返回最終結果集，該結果集是由非遞歸和遞歸項生成的第一次和第二次迭代中的所有結果集的并集。

employee_id | manager_id | full_name
------------+------------+-----------------
      2 |       1 | Megan Berry
       6 |       2 | Bella Tucker
       7 |       2 | Ryan Metcalfe
       8 |       2 | Max Mills
          9 |       2 | Benjamin Glover
      16 |       7 | Piers Paige
      17 |       7 | Ryan Henderson
      18 |       8 | Frank Tucker
      19 |       8 | Nathan Ferguson
      20 |       8 | Kevin Rampling
(10 rows)

以上是postgresql能不能進行遞歸查詢的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！