在 PostgreSQL 中真的沒有簡單的方法來實現“遞歸查找”嗎？

我有一個類別表：

id,label,parent_id
1,Animals,null
2,Dogs,1
3,Cats,1
4,Black,3

這意味著“Animals”是頂級類別，“Dogs”和“Cats”都是“Animals”的子類別，“Black”是“Cats”的子類別（本身就是一個子類別） “動物”）。它可以是任意數量的級別/“世代”。

坦率地說，我從沒想過這會是任何類型的問題，但是一旦我想創建一個查詢來計算“動物”類別中的所有對象（也意味著子類別中的所有對象），我就陷入了無望的死胡同。PostgreSQL 手冊和 Stack Exchange 問題以及其他線上資源都只是展示了這些WITH RECURSIVE充滿UNIONs 和所有內容的大量、令人困惑的查詢。我根本無法跟隨他們。

不可能這麼難。我一定是誤會了什麼。

我要做的就是：

SELECT count(*) FROM table WHERE id = $1 OR parent_id RECURSIVE = $1;

和：

SELECT id FROM table WHERE id = $1 OR parent_id RECURSIVE = $1;

自然，我在RECURSIVE這裡編造了關鍵字，但這就是我期望它起作用的方式。

我已經多次閱讀手冊和 Stack Exchange 的答案，但我只是不知道如何“適應”它以適應我的情況。我什至不明白他們在範例中在做什麼。簡直太他媽複雜了。這似乎是一個很常見的問題，它一定早就被優雅地解決了……對吧？

我認為，如果您了解基本的遞歸 CTE實現，將來您將不會發現它們如此難以理解。

我將嘗試向您展示如何以COUNT(*)非常非常簡單的步驟完成您提到的操作。

我們將找到所有屬於家庭Animal的 s 。Cat在這裡向任何分類學家道歉。

1. 首先，我們需要定義一個起始查詢：讓我們獲取 id Cat。我們將其放入 CTE（基本上是在查詢中定義的視圖）：

WITH cte AS (
   SELECT a.Id, a.Name
       FROM Animal AS a
       WHERE name = 'Cat'
)

結果：

2. 現在我們獲取上一個查詢的結果並UNION ALL（連接）下一層：

WITH Anchor AS (
   SELECT a.Id, a.Name
       FROM Animal AS a
       WHERE name = 'Cat'
),
Recursion AS (
   SELECT *
       FROM Anchor
   UNION ALL
   SELECT a.Id, a.Name
       FROM Animal AS a
       JOIN Anchor AS cte ON cte.id = a.parent_id
),

結果：

3. 這個階段的問題是我們必須為每個級別做一個新的查詢。所以我們需要結合這兩個查詢，並讓數據庫UNION一次又一次地保持每個結果。這就是魔法發生的地方：

WITH RECURSIVE cte AS (
   SELECT a.Id, a.Name
       FROM Animal AS a
       WHERE name = 'Cat'
   UNION ALL
   SELECT a.Id, a.Name
       FROM Animal AS a
       JOIN cte ON cte.id = a.parent_id
)

這裡發生的是 Anchor 部分（步驟 1）被執行並輸出。結果也回饋到遞歸部分的連接中。結果再次輸出並再次回饋給遞歸，直到遞歸返回空結果。

結果：

4. 這很簡單：我們計算最終結果

WITH RECURSIVE cte AS (
   SELECT a.Id, a.Name
       FROM Animal AS a
       WHERE name = 'Cat'
   UNION ALL
   SELECT a.Id, a.Name
       FROM Animal AS a
       JOIN cte ON cte.id = a.parent_id
)
SELECT COUNT(*)
FROM cte;

結果：

聽起來您在設計和思考問題時更多地是程序性的，而不是關係性的。您應該規範化您的數據，以便頂級類別Animals是它自己的表，Dogs並且Cats是表中的值Animals。然後這些屬性Animals可以是Animals表中的附加欄位，例如AnimalColor，或者您也可以將它們規範化為自己的表並通過 ID 引用它們，例如表Colors（或AnimalColors取決於其他對象可以引用 a Color）。然後，這會將您的問題（例如“有多少動物？ ”）簡化為基本查詢，例如SELECT COUNT(*) FROM Animals.

如果您必須將所有實體儲存在您提到的一個分層表下，那麼您需要使用遞歸 CTE 之類的東西。請參閱有關如何實施的基本演練。

---

下面是一個利用遞歸 CTE 的範例查詢：

WITH RECURSIVE CTE (child_id, child_label, parent_label, parent_id) AS (
   -- This is your recursion's base cass
   SELECT id AS child_id, label AS child_label, '' AS parent_label, parent_id
   FROM CategoriesTable

   UNION ALL

   -- This is the recursive case. Notice how the previous UNIONed data set becomes the child and the new join becomes the parent, and then on the next case this parent becomes the child and it's parent is joined to. Basically the current Parent becomes the Child of the next Parent on each iteration, as it crawls the hierarchy.
   SELECT C.parent_id AS child_id, C.label AS child_label, CT.label as parent_label, CT.parent_id 
   FROM CategoriesTable AS CT
   INNER JOIN CTE C
       ON C.parent_id = CT.id
)
-- This is your final result set, which currently is the entire hierarchy crawled. You can add a WHERE clause to filter on a certain subcategory's ancestor branch only, or you can do your aggregation here like COUNT(*) to count how many subcategories there are, etc.
SELECT child_id, child_label, parent_label, parent_id FROM CTE;

引用自：https://dba.stackexchange.com/questions/282924