MySQL:将联接后的多个值合并为一个结果列

Question

我有一个数据库，其中有一个用于发布的表，每个发布表可以有多个存储在不同表中的作者。我想查询数据库，使其在一列中给出出版物标题列表，在第二列中给出该出版物的组合作者。

SELECT p.`id`, p.`title`, a.`fullname` 
from `publications` p 
LEFT JOIN `authors` a on a.`publication_id` = p.`id`;

当然，这给了我数倍于许多作者的出版物标题。

id   title              fullname
--   -----              --------
1    Beneath the Skin   Sean French
1    Beneath the Skin   Nicci Gerrard
2    The Talisman       Stephen King
2    The Talisman       Peter Straub

根据id分组，每个标题有一个作者：

SELECT p.`id`, p.`title`, a.`fullname` 
from `publications` p 
LEFT JOIN `authors` a on a.`publication_id` = p.`id` 
GROUP BY a.`id`;

id   title              fullname
--   -----              --------
1    Beneath the Skin   Sean French
2    The Talisman       Stephen King

我想要的结果是：

id   title              fullname
--   -----              --------
1    Beneath the Skin   Sean French, Nicci Gerrard
2    The Talisman       Stephen King, Peter Straub

我认为答案应该在使用GROUP_CONCAT中找到，但我能得到的唯一结果是所有作者的一个结果行：

SELECT p.`id`, p.`title`, GROUP_CONCAT(a.`fullname`) from `publications` p 
LEFT JOIN `authors` a on a.`publication_id` = p.`id` 
GROUP BY a.`id`;

id   title              fullname
--   -----              --------
1    Beneath the Skin   Sean French, Nicci Gerrard, Stephen King, Peter Straub

在连接之后使用GROUP_CONCAT会给我一个“每个派生表必须有自己的别名”的错误。

SELECT p.`id`, p.`title`, a.`fullname` 
FROM `publications` p 
LEFT JOIN (SELECT GROUP_CONCAT(a.`fullname`) FROM `authors` a) ON a.`publication_id` = p.`id`;

有什么线索吗？

Answer 1

您需要按SELECT中的所有非聚集列进行分组(显式地，不要按author id分组，因为author是GROUP_CONCAT的一部分)：

SELECT p.`id`, p.`title`, GROUP_CONCAT(a.`fullname` separator ', ')
from `publications` p 
LEFT JOIN `authors` a on a.`publication_id` = p.`id` 
GROUP BY p.`id`, p.`title`;

Answer 2

斯图尔特的回答是好的。这只是为了展示您的方法的工作版本：

SELECT p.`id`, p.`title`, a.`fullname`
FROM `publications` p LEFT JOIN
      (SELECT publication_id, GROUP_CONCAT(a.`fullname` separator ', ')
       FROM `authors` a
       GROUP BY publication_id
      ) a
--------^
      ON a.`publication_id` = p.`id`;

您得到的错误是因为在子查询之后缺少a。还需要修改子查询，以便在select和group by子句中包含publication_id。