如何通过两个多对多关联在单条SQL查询中获取书籍全量信息？

问题场景 #

book表与genre、author_example表为多对多关联，通过中间表associate_book_genre、associate_book_author建立关联，需要用单条SELECT语句获取每本书的标题、合并后的作者列表、合并后的分类列表，且需包含所有书籍（即使无作者或分类）。

原查询的问题分析 #

你当前的查询存在三个核心问题：

1. 分组字段错误：使用GROUP BY g.id分组，会将无分类书籍（g.id为NULL）的聚合结果归为一组，同时可能将不同书籍但同分类的记录合并，导致数据丢失或错误。
2. 笛卡尔积导致重复：直接同时左连接作者和分类关联表，当一本书有多个作者和多个分类时，会生成作者-分类的笛卡尔积组合，导致GROUP_CONCAT出现重复内容，且结果行重复。
3. 缺失无关联数据的书籍：由于分组字段依赖分类表的ID，完全无作者和分类的书籍会被错误过滤。

正确查询方案 #

以下两种方案均可实现需求，避免上述问题：

方案一：预聚合作者/分类后关联主表

先分别对作者、分类按书籍ID聚合，再与book表左连接，彻底避免笛卡尔积：

SELECT 
    b.title AS `书籍标题`,
    COALESCE(a.authors, NULL) AS `作者`,
    COALESCE(g.genres, NULL) AS `分类`
FROM book b
LEFT JOIN (
    SELECT 
        aba.book_id,
        GROUP_CONCAT(ae.name SEPARATOR " & ") AS authors
    FROM associate_book_author aba
    JOIN author_example ae ON aba.author_id = ae.id
    GROUP BY aba.book_id
) a ON b.id = a.book_id
LEFT JOIN (
    SELECT 
        abg.book_id,
        GROUP_CONCAT(g.genre SEPARATOR ", ") AS genres
    FROM associate_book_genre abg
    JOIN genre g ON abg.genre_id = g.id
    GROUP BY abg.book_id
) g ON b.id = g.book_id
ORDER BY b.title;

方案二：SELECT子句中使用相关子查询

通过相关子查询直接在主查询中聚合对应书籍的作者和分类，写法更简洁：

SELECT 
    b.title AS `书籍标题`,
    (SELECT GROUP_CONCAT(ae.name SEPARATOR " & ") 
     FROM associate_book_author aba
     JOIN author_example ae ON aba.author_id = ae.id
     WHERE aba.book_id = b.id) AS `作者`,
    (SELECT GROUP_CONCAT(g.genre SEPARATOR ", ")
     FROM associate_book_genre abg
     JOIN genre g ON abg.genre_id = g.id
     WHERE abg.book_id = b.id) AS `分类`
FROM book b
ORDER BY b.title;

效果说明 #

两种方案均会返回所有4本目标书籍，其中：

• 无作者/分类的书籍对应字段显示为NULL
• 多作者/多分类的内容按指定分隔符合并
• 无重复行，数据与期望输出完全匹配

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