PostgreSQL查询：筛选符合特定重复标签规则的tag_id

需求背景 #

现有一张PostgreSQL表（假设表名为tags），包含tag_id、duplicate_tag_id、tag_created_at_timestamp字段，数据如下：

tag_id,  duplicate_tag_id, tag_created_at_timestamp
14175,   14178,            ...
14175,   14177,            ...
14176,   null,             ...
14177,   14178,            ...
14178,   14179,            ...
14179,   null,             ...
14180,   null,             ...
14181,   null,             ...

需要编写查询语句，返回满足以下条件的tag_id：该tag_id未在duplicate_tag_id列中出现，除非对应的tag_id已在之前行的duplicate_tag_id中存在（即它属于某条重复链的最终节点）。

预期查询结果：

14175
14176
14179
14180
14181

说明：14179被包含是因为它是14178的重复标签，而14178本身已是14175的重复标签，属于重复链的最终节点

解决方案 #

基础版（适用于简单层级） #

通过CTE（公共表表达式）拆分逻辑，直接筛选符合条件的标签：

WITH referenced_tags AS (
    -- 提取所有被其他标签标记为重复的tag_id
    SELECT DISTINCT duplicate_tag_id AS tag_id
    FROM tags
    WHERE duplicate_tag_id IS NOT NULL
),
final_nodes AS (
    -- 提取所有没有指向重复标签的最终节点
    SELECT tag_id
    FROM tags
    WHERE duplicate_tag_id IS NULL
)
-- 合并两类符合条件的tag_id并排序
SELECT tag_id
FROM tags
WHERE tag_id NOT IN (SELECT tag_id FROM referenced_tags)
UNION
SELECT tag_id FROM final_nodes
WHERE tag_id IN (SELECT tag_id FROM referenced_tags)
ORDER BY tag_id;

递归版（适用于多层嵌套重复链） #

如果存在更深层级的重复关系，用递归CTE追溯每个标签的根节点与最终节点，确保覆盖所有复杂场景：

WITH RECURSIVE tag_hierarchy AS (
    -- 初始节点：所有没有指向重复标签的tag
    SELECT tag_id, tag_id AS root_id, tag_id AS final_id
    FROM tags
    WHERE duplicate_tag_id IS NULL
    UNION ALL
    -- 递归向上追溯父节点，关联层级关系
    SELECT t.tag_id, th.root_id, th.final_id
    FROM tags t
    JOIN tag_hierarchy th ON t.duplicate_tag_id = th.tag_id
),
tag_relations AS (
    -- 去重后得到每个tag对应的根节点和最终节点
    SELECT DISTINCT tag_id, root_id, final_id
    FROM tag_hierarchy
),
valid_tags AS (
    -- 筛选根节点和最终节点
    SELECT root_id AS tag_id FROM tag_relations
    UNION
    SELECT final_id AS tag_id FROM tag_relations
)
SELECT tag_id FROM valid_tags ORDER BY tag_id;

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