如何在PostgreSQL中编写递归UNION ALL查询处理未知层级关联？

现有存储两两关联关系的表related_entities，结构及数据如下：

需要编写SQL查询，生成包含起始节点100000与所有直接、间接关联节点的配对结果表，格式如下：

补充说明：已知当关联层级最多3层时，可通过多次JOIN加UNION ALL实现，示例代码如下：

select *
from related_entities

union all

select r1.column_a, r2.column_b
from related_entities r1
join related_entities r2 on r2.column_a = r1.column_b
;

但实际场景中关联层级未知（最多可能到多层），需要一种高效的递归写法来解决这个问题。

解决方案：递归CTE（公共表表达式） #

这是处理未知层级关联查询的标准高效方法，主流数据库（MySQL 8.0+、PostgreSQL、SQL Server等）均支持，具体SQL如下：

WITH RECURSIVE entity_hierarchy AS (
    -- 基准查询：先拿起始节点的直接关联记录
    SELECT column_a, column_b
    FROM related_entities
    WHERE column_a = 100000

    UNION ALL

    -- 递归查询：逐层找间接关联节点，始终保留起始节点作为column_a
    SELECT eh.column_a, re.column_b
    FROM entity_hierarchy eh
    JOIN related_entities re ON re.column_a = eh.column_b
)
SELECT * FROM entity_hierarchy;

逻辑说明：

1. RECURSIVE关键字开启递归功能，CTE分为基准部分和递归部分
2. 基准部分先取出起始节点100000的直接关联数据
3. 递归部分把已找到的关联节点的column_b作为新起点，关联表中获取下一层节点，同时始终用起始节点作为结果的column_a，直到没有更多关联节点为止
4. 最终查询递归CTE的结果，就能得到所有符合要求的配对

防循环处理（可选）

如果业务场景中存在循环关联的可能，可增加字段记录已访问节点，避免重复或死循环：

WITH RECURSIVE entity_hierarchy AS (
    SELECT 
        column_a, 
        column_b,
        ARRAY[column_a, column_b] AS visited_nodes -- 用数组记录已访问的节点
    FROM related_entities
    WHERE column_a = 100000

    UNION ALL

    SELECT 
        eh.column_a, 
        re.column_b,
        eh.visited_nodes || re.column_b
    FROM entity_hierarchy eh
    JOIN related_entities re ON re.column_a = eh.column_b
    WHERE re.column_b <> ALL(eh.visited_nodes) -- 过滤已访问过的节点
)
SELECT column_a, column_b FROM entity_hierarchy;

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