如何通过SQL查询获取树结构中指定节点的最高祖先

我有两张SQL表，结构如下：

表结构 #

tag_hierarchies表 #

CREATE TABLE tag_hierarchies (
    ancestor_id integer NOT NULL,
    descendant_id integer NOT NULL,
    generations integer NOT NULL
);

其中generations = 0代表根节点（自身是自己的祖先）。

tags表 #

CREATE TABLE tags (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR
);

示例数据 #

tag_hierarchies表数据 #

tags表数据 #

上述数据对应树结构：a/b/c/d 和独立节点 e。我需要编写一个SQL查询，针对指定的标签集合，返回每个树中**唯一的最低代（即最高祖先）**标签。比如当查询标签为b、c、e时，应返回b和e——因为b是c的祖先，属于同一树，取代更低的b；e自身是根节点，直接返回。

推荐使用以下简洁的查询实现需求：

WITH target_tags AS (
    -- 替换为你需要查询的标签集合
    SELECT id FROM tags WHERE name IN ('b', 'c', 'e')
)
SELECT t.id, t.name
FROM tags t
JOIN target_tags tt ON t.id = tt.id
WHERE NOT EXISTS (
    SELECT 1
    FROM tag_hierarchies th
    JOIN target_tags tt2 ON th.ancestor_id = tt2.id
    WHERE th.descendant_id = t.id
      AND th.generations > 0 -- 排除自身作为祖先的情况
);

逻辑说明 #

1. target_tags CTE：先筛选出目标标签对应的ID集合，修改IN括号内的内容即可切换不同的查询标签。如果需要通过ID指定目标，可改为SELECT id FROM tags WHERE id IN (85, 86, 88)。
2. 主查询：从目标标签中，排除那些存在其他目标标签是它的祖先的标签。剩下的就是每个树中在目标集合里的最高祖先（最低代）——比如示例中的b（c的祖先且在目标集合中，所以c被排除）、e（无其他目标标签是它的祖先）。

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