基于people和relations表关联查询孩子及父母姓名的SQL编写问题

CREATE TABLE people (
    id INT,
    name VARCHAR(20),
    gender VARCHAR(1)
);

CREATE TABLE relations (
    c_id INT,
    p_id INT
);

INSERT INTO people VALUES(145, 'HAWBAKER' , 'F');
INSERT INTO relations VALUES(145, 202);
INSERT INTO relations VALUES(145, 107);

INSERT INTO people VALUES(202, 'Blackston' , 'F');
INSERT INTO people VALUES(107, 'Days' , 'M');

SELECT * FROM people;
SELECT * FROM relations;

查询结果需包含child、father、mother三个字段，分别对应孩子姓名、父亲姓名、母亲姓名，结果按孩子姓名排序输出。

SELECT p.name, r.p_id
FROM people p JOIN relations r 
ON ( r.c_id = p.id);

SELECT 
    p_child.name AS child,
    MAX(CASE WHEN p_parent.gender = 'M' THEN p_parent.name END) AS father,
    MAX(CASE WHEN p_parent.gender = 'F' THEN p_parent.name END) AS mother
FROM people p_child
INNER JOIN relations r ON p_child.id = r.c_id
INNER JOIN people p_parent ON r.p_id = p_parent.id
GROUP BY p_child.id, p_child.name
ORDER BY p_child.name ASC;

语句逻辑：先关联拿到孩子对应的所有父母记录，再通过性别判断分别提取父亲和母亲的姓名，按孩子维度分组去重后完成排序即可。

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