面试SQL问题：基于父母表与关系表生成规整亲子输出

需求说明 #

现有两张表：

• parents表：存储人员ID与姓名
• relations表：存储孩子ID与父母ID的关联关系
  需要基于这两张表，输出孩子姓名、父亲姓名、母亲姓名的结构化结果。

输入表数据 #

parents表 #

id  name
1   yogi
2   sidda
4   arpitha
5   sushma
6   navya
7   divya
8   sanju
9   ashwin
10  chetu
11  seena
12  vindi

relations表 #

child_id parent_id
4   1
4   2
5   10
5   6
7   8
7   9
11  12
11  13

现有SQL及问题 #

编写的SQL语句：

select child,p2.name1, from parents p2 join 
( select p1.name1 as child,r1.p_id,r1.c_id,p1.id1 from parents p1 join (select * from parents join relations on p_id=id1) r1 on p1.id1=r1.c_id) r2 on p2.id1=p_id;

该语句输出结果（每行对应孩子与一位父母）：

child parents
arpita yogi
arpita sidda 
sushma chetu 
sushma navya 
divya sanju
divya ashwin 
seena vindi 
seena varshini

期望输出 #

child   father  mother
arpitha yogi    sidda
sushma  chetu   navya
divya   sanju   ashwin
seena   vindi   varshini

解决方案 #

要实现将同一孩子的两位父母合并到一行的效果，可以采用条件聚合或自连接的方式：

方法1：条件聚合（通用兼容多数SQL数据库） #

通过给每个孩子的父母记录编号，再分组聚合提取父亲、母亲姓名：

SELECT
    p_child.name AS child,
    MAX(CASE WHEN rn = 1 THEN p_parent.name END) AS father,
    MAX(CASE WHEN rn = 2 THEN p_parent.name END) AS mother
FROM (
    -- 给每个孩子的父母记录按parent_id排序编号
    SELECT
        child_id,
        parent_id,
        ROW_NUMBER() OVER(PARTITION BY child_id ORDER BY parent_id) AS rn
    FROM relations
) r
-- 关联获取孩子姓名
JOIN parents p_child ON r.child_id = p_child.id
-- 关联获取父母姓名（处理parent_id不存在的情况用LEFT JOIN）
LEFT JOIN parents p_parent ON r.parent_id = p_parent.id
-- 按孩子分组聚合
GROUP BY p_child.id, p_child.name
ORDER BY p_child.id;

方法2：自连接（适用于每个孩子固定两位父母的场景） #

通过自连接relations表，将同一孩子的两位父母关联到一行：

SELECT
    p_child.name AS child,
    p_father.name AS father,
    p_mother.name AS mother
FROM relations r1
-- 自连接关联同一孩子的另一位父母，用parent_id大小区分避免重复
JOIN relations r2 ON r1.child_id = r2.child_id AND r1.parent_id < r2.parent_id
-- 关联孩子姓名
JOIN parents p_child ON r1.child_id = p_child.id
-- 关联第一位父母（父亲）
JOIN parents p_father ON r1.parent_id = p_father.id
-- 关联第二位父母（母亲）
JOIN parents p_mother ON r2.parent_id = p_mother.id;

原SQL问题分析 #

原SQL通过多层JOIN实现了孩子与父母的关联，但未做行转列处理，导致每个孩子对应两行记录（每行一位父母）。上述两种方法通过分组聚合或自连接，将同一孩子的两位父母信息合并到一行，满足结构化输出需求。

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