postgresql中記錄的分層檢索

我正在使用 postgresql-9.2 。我有一個產品表，其中儲存有關產品的資訊，它的子表是 content table ，其中包含連結到它的產品。

產品主表

MASTER_ID                NAME 

1                      PRODUCT1
2                      PRODUCT2
3                      PRODUCT3
4                      PRODUCT4
5                      PRODUCT5
6                      PRODUCT6
7                      PRODUCT7
8                      PRODUCT8
9                      PRODUCT9
10                     PRODUCT10

PRODUCT_目錄

PRODUCT_CONTENT_ID                     PRODUCT_MASTER_ID        CONTENT_ID
A1                                          1                       2
A2                                          1                       3
A3                                          1                       4
A4                                          2                       3
A5                                          2                       4
A6                                          2                       5
A7                                          3                       5
A8                                          3                       7
A9                                          3                       8
A10                                         5                       8
A11                                         7                       9
A12                                         8                       10

我正在編寫一個查詢來檢索所有產品（與它們關聯的 id）及其內容產品，這些產品應該顯示類似這種層次結構的內容

MAINPRODUCT                     SUBPRODUCT
   2                               3
   2                               4
   2                               5
   3                               5
   3                               7
   3                               8
   5                               8   
   7                               9
   8                               10

我寫了以下查詢

SELECT PM1.PRODUCT_MASTER_ID AS MAIN,PC.CONTENT_ID AS CONTENT
   FROM PRODUCT_CONTENT PC 
   LEFT JOIN PRODUCT_MASTER PM ON PM.PRODUCT_MASTER_ID = PC.CONTENT_ID
   LEFT JOIN PRODUCT_MASTER PM1 ON 
   PM1.PRODUCT_MASTER_ID = PC.PRODUCT_MASTER_ID
   WHERE PC.PRODUCT_MASTER_ID IN 
     (
      SELECT PC.CONTENT_ID FROM PRODUCT_CONTENT PC 
      LEFT JOIN PRODUCT_MASTER PM ON 
         PM.PRODUCT_MASTER_ID = PC.CONTENT_ID
      LEFT JOIN PRODUCT_MASTER PM1 ON 
         PM1.PRODUCT_MASTER_ID = PC.PRODUCT_MASTER_ID
      WHERE PC.PRODUCT_MASTER_ID IN ('1')
     )

它返回這樣的東西

MAIN            CONTENT

2                       3
2                       4
2                       5
3                       5
3                       7
3                       8

這是一個簡單的鄰接表遍歷問題。您甚至沒有嘗試建構路徑，只需從根節點或集合中查找節點。這幾乎是遞歸 CTE 用途的教科書範例。

http://sqlfiddle.com/#!12/8c406/12

WITH RECURSIVE children(product_content_id, product_master_id, content_id) AS (
 SELECT pc.product_content_id, pc.product_master_id, pc.content_id
 FROM product_content pc
 WHERE pc.product_master_id = '1'
 UNION
 SELECT pc.product_content_id, pc.product_master_id, pc.content_id
 FROM product_content pc
 INNER JOIN children c ON (pc.product_master_id = c.content_id)
)
SELECT
 product_master_id AS mainproduct,
 content_id AS subproduct
FROM children;

您幾乎可以從PostgreSQL 手冊中調整這一點，其中包括一個關於邊緣列表圖遍歷的範例。

---

數據庫設計旁注：請不要將字元欄位用於數字。A10如果Aand10部分根本沒有任何單獨的含義，那麼使用鍵通常也不是一個好主意。

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