PostgreSQL中能否编写自下而上的递归CTE查询上级区域？

首先是我们使用的territories表结构：

CREATE TABLE territories 
(
    id serial PRIMARY KEY,
    name varchar NOT NULL,
    containing_id int
);

CREATE INDEX container_id_index ON territories (containing_id);

示例数据如下：

INSERT INTO territories (id, name, containing_id)
VALUES
    (1, 'Earth', NULL),
    (2, 'North America', 1),
    (3, 'South America', 1),
    (4, 'Africa', 1),
    (5, 'Asia', 1),
    (6, 'United States', 2),
    (7, 'Canada', 2),
    (8, 'Mexico', 2),
    (10, 'Georgia', 6),
    (11, 'Atlanta', 10),
    (12, 'Nigeria', 4),
    (13, 'Abuja', 12),
    (14, 'Chile', 3),
    (15, 'Santiago', 14),
    (16, 'Ontario', 7),
    (17, 'Toronto', 16),
    (19, 'Cambodia', 5),
    (20, 'Phnom Penh', 19);

已经可以通过自上而下的递归CTE列出指定起点下的所有子区域，比如以“Earth”为起点的查询：

-- 自上而下查询
WITH RECURSIVE superterritories AS 
(
    SELECT
        id,
        containing_id,
        name,
        NULL::varchar AS superterritory_name
    FROM
        territories
    WHERE
        name = 'Earth'
    UNION
    SELECT
        t.id,
        t.containing_id,
        t.name,
        sup.name AS superterritory_name
    FROM
        territories t
    INNER JOIN 
        superterritories sup ON sup.id = t.containing_id
)
SELECT
    *
FROM
    superterritories;

查询输出：

id | containing_id |     name      | superterritory_name
----+---------------+---------------+-----------------
  1 |        [NULL] | Earth         | [NULL]
  2 |             1 | North America | Earth
  3 |             1 | South America | Earth
  4 |             1 | Africa        | Earth
  5 |             1 | Asia          | Earth
  6 |             2 | United States | North America
  7 |             2 | Canada        | North America
  8 |             2 | Mexico        | North America
 12 |             4 | Nigeria       | Africa
 14 |             3 | Chile         | South America
 19 |             5 | Cambodia      | Asia
 10 |             6 | Georgia       | United States
 13 |            12 | Abuja         | Nigeria
 15 |            14 | Santiago      | Chile
 16 |             7 | Ontario       | Canada
 20 |            19 | Phnom Penh    | Cambodia
 11 |            10 | Atlanta       | Georgia
 17 |            16 | Toronto       | Ontario
(18 rows)

如果将WHERE条件改为name = 'United States'，输出结果为：

id | containing_id |     name      | superterritory_name
----+---------------+---------------+-----------------
  6 |             2 | United States | [NULL]
 10 |             6 | Georgia       | United States
 11 |            10 | Atlanta       | Georgia

自下而上的递归CTE实现 #

当然可以编写自下而上的递归CTE，用来列出特定区域的所有上级区域。核心思路是从目标区域开始，不断向上关联containing_id对应的父区域，直到没有上级（containing_id为NULL）为止。

以下是实现代码，以查询“Atlanta”的所有上级区域为例：

-- 自下而上查询
WITH RECURSIVE sub_territories AS
(
    -- 初始查询：获取目标区域
    SELECT
        id,
        containing_id,
        name,
        NULL::varchar AS sub_territory_name
    FROM territories
    WHERE name = 'Atlanta'
    UNION
    -- 递归部分：关联父区域
    SELECT
        t.id,
        t.containing_id,
        t.name,
        sub.name AS sub_territory_name
    FROM territories t
    INNER JOIN sub_territories sub ON t.id = sub.containing_id
)
SELECT * FROM sub_territories;

查询输出 #

执行上述查询后，会得到Atlanta的所有上级区域，结果如下：

id | containing_id |     name      | sub_territory_name
----+---------------+---------------+-------------------
 11 |            10 | Atlanta       | [NULL]
 10 |             6 | Georgia       | Atlanta
  6 |             2 | United States | Georgia
  2 |             1 | North America | United States
  1 |        [NULL] | Earth         | North America
(5 rows)

你可以修改初始查询中的WHERE name = 'Atlanta'条件，替换为任意区域名称，来获取对应区域的所有上级层级。

内容的提问来源于stack exchange，提问作者Nathan Long