如何从两张关联表查询所有子女对应的父亲母亲姓名信息

解题思路 #

核心是对每个子女的父母设置排序规则，按优先级拆分父亲和母亲：

• 优先把性别为男（GENDER='M'）的父母排为第一位作为父亲
• 若父母均为女性，按姓名长度升序排序，长度更短的为父亲，更长的为母亲
• 最后按子女分组聚合，分别取出排序第一、第二位的父母姓名转为小写即可匹配预期输出。

参考SQL代码 #

WITH parent_info AS (
    SELECT 
        r.c_id,
        p.name AS parent_name,
        p.gender,
        -- 按规则给父母排序：男性优先，否则姓名长度短的优先
        ROW_NUMBER() OVER (
            PARTITION BY r.c_id 
            ORDER BY CASE WHEN p.gender = 'M' THEN 1 ELSE 2 END, LENGTH(p.name)
        ) AS rn
    FROM relations r
    JOIN people p ON r.p_id = p.id
)
SELECT 
    LOWER(c.name) AS child,
    LOWER(MAX(CASE WHEN rn = 1 THEN parent_name END)) AS father,
    LOWER(MAX(CASE WHEN rn = 2 THEN parent_name END)) AS mother
FROM parent_info pi
JOIN people c ON pi.c_id = c.id
GROUP BY pi.c_id, c.name
ORDER BY child;

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