MySQL:将联接后的多个值合并到一个结果列中 [英] MySQL: combining multiple values after a join into one result column

问题描述

我有一个数据库，其中有一个用于发布的表格，每个表格可以有多个作者，这些作者存储在不同的表格中.我想查询数据库，以便在第一栏中为我提供出版物标题列表，在第二栏中为该出版物的合并作者.

I have a database with a table for publications, each of which can have multiple authors that are stored in a different table. I'd like to query the database into giving me a list of publication titles in one column, and the combined authors for that publication in the second.

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

这当然给了我很多作者的出版物标题.

This of course gives me multiple times the publication title for as many authors.

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分组后，每个标题给我一个作者:

Grouping on id gives me one author per title:

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

我要寻找的结果是这样:

The result I'm looking for is this:

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

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

I think the answer should be found in using GROUP_CONCAT, but the only result I'm able to get is one result row with all authors:

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会给我一个每个派生表必须具有自己的别名"的错误.

And using GROUP_CONCAT after the join gives me an "Every derived table must have its own alias" error.

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`;

有任何线索吗?

推荐答案

您需要对SELECT列中的所有非聚合列进行分组(并且明确地不是作者ID，因为author.id不属于选择列表):

You need to group by all of the non-aggregated columns in the SELECT columns (and explicitly, not the author id, because author.id is not part of the select list):

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`;

这篇关于MySQL:将联接后的多个值合并到一个结果列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！