如何在层级父子关系表中查找所有关联问题并分配组号

解决方案 #

该需求本质为无向图连通分量识别，我们通过递归CTE实现，同时规避循环引用问题，最终输出符合要求的分组结果，适配所有支持递归CTE的SQL数据库（MySQL 8.0+、PostgreSQL、SQL Server等）。

实现代码 #

WITH RECURSIVE
-- 提取所有独立节点
all_nodes AS (
    SELECT Parent AS node FROM 父子关系表
    UNION
    SELECT Child AS node FROM 父子关系表
),
-- 构造无向边，支持双向遍历
undirected_edges AS (
    SELECT Parent AS u, Child AS v FROM 父子关系表
    UNION ALL
    SELECT Child AS u, Parent AS v FROM 父子关系表
),
-- 递归计算连通分量，取每个分量最小节点ID为临时组号
connected_groups AS (
    -- 锚点：每个节点初始组号为自身ID
    SELECT node AS issue, node AS tmp_group FROM all_nodes
    UNION ALL
    -- 递归：仅向更小的组号合并，避免循环
    SELECT e.v AS issue, cg.tmp_group
    FROM connected_groups cg
    JOIN undirected_edges e ON cg.issue = e.u
    WHERE e.v > cg.tmp_group
)
-- 输出最终结果，连续编号组号
SELECT 
    issue AS Issue,
    DENSE_RANK() OVER(ORDER BY MIN(tmp_group)) AS `Group`
FROM connected_groups
GROUP BY issue
ORDER BY issue;

逻辑说明 #

1. 先提取所有独立节点，同时把原父子关系转换为双向无向边，无需单独处理向上/向下追溯的逻辑。
2. 递归阶段给每个节点初始分配自身ID为临时组号，仅当相邻节点的ID大于当前组号时才触发合并，组号只会不断变小，从根源避免循环引用。
3. 最后通过DENSE_RANK窗口函数把临时组号转换为连续的分组编号，和示例输出格式完全匹配。

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