如何用SQL生成同组家庭成员关联组合并支持后续遍历

我有一张包含4列的表，列分别为Group_ID、Contact_ID、F_Name、Relationship。需要生成每个Group_ID对应的家庭组内所有成员的关联组合，原表包含数千条记录及多个家庭组，请问如何编写可支持后续数据遍历的初始SQL语句？

原表示例：

期望输出：

可以通过自连接结合条件判断实现需求，核心思路是将表与自身按Group_ID关联，生成同一家庭组内的所有成员配对，再通过逻辑映射生成对应的关系描述。以下是可直接使用的SQL语句（假设表名为family_contacts）：

SELECT
    a.Contact_ID,
    CONCAT(
        b.Contact_ID,
        '    (',
        a.F_Name,
        ' is ',
        CASE
            -- 配偶关系映射
            WHEN a.Relationship = 'Father' AND b.Relationship = 'Mother' THEN 'Husband'
            WHEN a.Relationship = 'Mother' AND b.Relationship = 'Father' THEN 'Wife'
            -- 父母与子女关系映射
            WHEN a.Relationship = 'Father' AND b.Relationship IN ('Son', 'Daughter') THEN 'Father'
            WHEN a.Relationship = 'Mother' AND b.Relationship IN ('Son', 'Daughter') THEN 'Mother'
            WHEN a.Relationship IN ('Son', 'Daughter') AND b.Relationship = 'Father' THEN 'Son'
            WHEN a.Relationship IN ('Son', 'Daughter') AND b.Relationship = 'Mother' THEN 'Daughter'
            -- 兄弟姐妹关系映射
            WHEN a.Relationship = 'Son' AND b.Relationship = 'Daughter' THEN 'Brother'
            WHEN a.Relationship = 'Daughter' AND b.Relationship = 'Son' THEN 'Sister'
            WHEN a.Relationship = 'Son' AND b.Relationship = 'Son' THEN 'Brother'
            WHEN a.Relationship = 'Daughter' AND b.Relationship = 'Daughter' THEN 'Sister'
            -- 未定义关系可自定义默认值
            ELSE ''
        END,
        ' to ',
        b.F_Name,
        ')'
    ) AS Relation_ID
FROM
    family_contacts a
JOIN
    family_contacts b ON a.Group_ID = b.Group_ID
WHERE
    a.Contact_ID != b.Contact_ID
ORDER BY
    a.Contact_ID, b.Contact_ID;

逻辑说明 #

1. 自连接：通过a.Group_ID = b.Group_ID确保只处理同一家庭组内的成员配对。
2. 过滤条件：a.Contact_ID != b.Contact_ID排除成员与自身的无效配对。
3. 关系映射：使用CASE语句根据双方的Relationship字段，生成符合期望的关系描述，覆盖配偶、父母子女、兄弟姐妹三类核心家庭关系。
4. 结果格式化：通过CONCAT函数将关联ID和关系描述拼接成目标格式。

扩展说明 #

• 如果存在更多关系类型（如祖父、叔叔等），只需在CASE语句中添加对应的映射条件即可。
• 对于数千条记录，只要为Group_ID字段建立索引，自连接的性能可满足需求，生成的结果集可直接用于后续数据遍历。

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