獲取類別的產品以及所有子類別直到葉節點

我們有一個看起來像這樣的層次結構：

現在的問題是大多數產品只與 1 個類別相關聯，這在大多數情況下是最低級別。

我正在嘗試獲取給定類別以下的所有產品。所以如果選擇一個類別，我想要目前類別的所有產品和下面的所有類別。所以這不起作用：

SELECT "category"."id"
   ,"products"."name"
FROM "products"
INNER JOIN "product_categories" ON "products"."id" = "product_categories"."product_id"
INNER JOIN "categories" ON "product_categories"."category_id" = "categories"."id"
WHERE "categories"."parent_id" = 0

誰能幫幫我？順便說一句，我們正在使用 PostgreSQL

編輯：

我們正在使用“PostgreSQL 9.3.5”這些是這種情況下的表格，您可以在我上面的查詢（以及 ERD 下面）中看到我想要完成哪些連接。

編輯 2：這是我對 mysql 的查詢（這裡我們使用分類法而不是類別）：

SELECT T2.id, T2.taxonomy, T2.parent, T2.terms_id, terms.term
FROM (
   SELECT
       @r AS _id,
       (SELECT @r := parent FROM term_taxonomies WHERE id = _id) AS parent,
       @l := @l + 1 AS lvl
   FROM
       (SELECT @r := 1, @l := 0) vars,
       term_taxonomies m
   WHERE @r <> 0) T1
JOIN term_taxonomies T2
ON T1._id = T2.id
JOIN terms ON T2.terms_id = terms.id
ORDER BY T1.lvl DESC;

但這僅適用於mysql

如果您的資料結構是固定的並且不受您的控制，那麼最好的方法是使用遞歸公用表表達式 (CTE)，就像在這個問題中一樣。

如果您可以更改結構，那麼有一些方法可以大大提高此類查詢的效率。你所擁有的通常被稱為“幼稚樹” - 雖然它允許輕鬆建構和一次輕鬆上下導航，但更複雜的查詢，包括那些需要考慮任意深度的查詢（比如你的問題）可能是疼痛。

常見的替代方案是：

• 儲存每個節點的完整路徑以及父關係，並對其進行索引。這會使您的數據稍微反規範化，但會使“此節點關閉”查詢變得容易，因為您可以搜尋WHERE tree_path LIKE current_node_path+'%'或排除目前節點並僅列出其下方的節點WHERE tree_path LIKE current_node_path+'/%'。當然，當節點移動時，您還有更新路徑的額外工作 - 這可能最好通過觸發器來完成，或者如果您的應用程序總是使用儲存的過程來更新樹而不是直接訪問表，那麼很明顯（並且可能更有效）包括那裡的邏輯。
• 儲存關係矩陣，因此對於樹 1->2, 1->3, 2->4, 2->5, 5->6 您將儲存：

n1 n2 depth 2 1 1 3 1 1 4 2 1 4 1 2 5 2 1 5 1 2 6 5 1 6 2 2 6 1 3

同樣，您還有額外的數據需要維護，但它可以確保各種查詢更高效，更容易編寫。對於節點 5 下的所有人來說，它是WHERE n1=5. 有些人也會儲存自關係（即 n1=5，n2=5，d=0）以避免unions 當你想要節點和所有以下時 - 然後“5及以下”是WHERE n1=5，“5以下”是WHERE n1=5 AND depth>0。

本書中有一個很好的章節更詳細地介紹了這一點，我建議任何使用數據庫的人通讀一遍——它對於相對缺乏經驗的人來說有很好的語氣，有經驗的人會被提醒他們忘記的重要事情！如果你想深入了解，有整本關於在 SQL 和其他關係系統中處理分層數據的書籍*。*通常的推薦是“Joe Celko’s Trees and Hierarchies in SQL for Smarties”。

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