如何创建MySQL视图family实现父子表的子女聚合展示？

现有表结构及数据 #

表father #

ID  NAME
1   John
2   Paul
3   Mike

表child #

ID  FATHER  CHILD
1   1       Mary
2   2       Liz
3   2       Lucy

需求目标 #

创建名为family的视图，输出格式如下：

FATHER_ID FATHER_NAME  CHILDREN
1         John         'Mary'
2         Paul         'Liz, Lucy'
3         Mike         NULL

解决方案SQL #

CREATE VIEW family AS
SELECT
    f.ID AS FATHER_ID,
    f.NAME AS FATHER_NAME,
    CONCAT('\'', GROUP_CONCAT(c.CHILD SEPARATOR ', '), '\'') AS CHILDREN
FROM father f
LEFT JOIN child c ON f.ID = c.FATHER
GROUP BY f.ID, f.NAME;

关键说明 #

• 使用LEFT JOIN关联两张表，确保没有孩子的父亲（如ID为3的Mike）也能被包含在结果中，不会被过滤掉
• GROUP_CONCAT函数负责将同一个父亲的多个孩子名字用, 分隔拼接，再通过CONCAT给拼接结果前后加上单引号，匹配需求中的格式
• 当父亲没有对应孩子时，GROUP_CONCAT返回NULL，最终CHILDREN字段也会显示NULL，符合要求

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