postgresql - 如何使用postgres以递归模式获取其他记录映射的子类别ID？

Question

category_id | category_name | category_parent_id
3           | All           |   NULL
4           | Stationary    |    3
5           | Food          |    3
6           | Solid         |    5
7           | Liquid        |    5
8           | Soft Drinks   |    7
9           | Pepsi         |    8
10          | 7up           |    8
11          | Hot Item      |    7
12          | Tea           |   11
14          | Samusa        |    6

如果我给类别 id 是 7，我希望输出为

category_id | category_name | category_parent_id
7           | Liquid        |    5
8           | Soft Drinks   |    7
11          | Hot Item      |    7
9           | Pepsi         |    8
10          | 7up           |    8
12          | Tea           |   11
15          | Can(100Ml)    |   10
16          | Can(250ML)    |   10

我们需要以递归模式列出类别 id 7 的所有子类别及其子类别。

标签： postgresqlrecursive-query

Answer 1

您可能想看看CTE：

WITH RECURSIVE 
  mapping(category_parent_id,category_id,category_name) AS (
   SELECT category_parent_id,category_id,category_name FROM t
   UNION 
   SELECT mapping.category_parent_id, t.category_id, t.category_name FROM mapping, t
     WHERE mapping.category_id = t.category_parent_id
) SELECT category_id, category_name FROM mapping WHERE category_parent_id = 7;

 category_id | category_name 
-------------+---------------
           8 | Soft Drinks
          11 | Hot Item
          10 | 7up
           9 | Pepsi
          12 | Tea
(5 Zeilen)