MySQL通过关联表实现父子人员数据按家庭分组排序查询

我懂你想要的效果——把同一个家庭的成员聚在一起展示，先列父级信息，再依次列出他们的所有子级，每个家庭自成一组。你当前的查询虽然能拉取到关联数据，但排序逻辑没踩准点：order by b.pid2只能按父ID排序，没法区分父级和子级的先后顺序，也没法实现真正的家庭分组。

这里推荐用**递归CTE（公共表表达式）**来处理，这种方式天生适合拆解层级关系的数据，能完美实现你要的排序和分组效果。

具体实现代码 #

WITH RECURSIVE family_hierarchy AS (
    -- 第一步：找出所有顶层父级（没有被其他人作为子节点的成员）
    SELECT 
        p.ID,
        p.name,
        p.surname,
        p.art,
        NULL AS parent_id,
        0 AS member_level,
        p.ID AS family_root_id
    FROM Persons p
    WHERE NOT EXISTS (
        SELECT 1 FROM Relations r WHERE r.pid1 = p.ID
    )
    UNION ALL
    -- 第二步：递归关联所有子级成员
    SELECT 
        p.ID,
        p.name,
        p.surname,
        p.art,
        r.pid2 AS parent_id,
        fh.member_level + 1 AS member_level,
        fh.family_root_id
    FROM Persons p
    JOIN Relations r ON p.ID = r.pid1
    JOIN family_hierarchy fh ON r.pid2 = fh.ID
)
-- 最终查询并按要求排序
SELECT 
    ID,
    name,
    surname,
    art,
    parent_id
FROM family_hierarchy
ORDER BY family_root_id, member_level, name;

代码逻辑说明 #

1. 锚点查询：先筛选出所有没有上级的顶层成员（也就是家庭里的最长辈），给他们标记层级为0，并把他们的ID作为整个家庭的family_root_id（家庭标识）。
2. 递归关联：通过Relations表把每个父级的子级关联进来，子级的层级是父级层级+1，同时继承父级的family_root_id——这样同一个家庭的所有成员，family_root_id都是一样的。
3. 排序规则：最后按family_root_id分组（确保同一个家庭的成员在一起），再按member_level排序（层级0的父级先显示，之后是层级1的子级），最后加个name排序可以让子级的展示更整齐。

如果你的数据里存在多层级关系（比如祖辈→父母→孩子），这个递归CTE也能自动处理，会从上到下按层级展示整个家族的成员。

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