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中的所有非聚合列进行分组（并且明确地，不按作者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`;