SQL多表联查如何将同一书籍的多行作者结果合并为逗号分隔的单行

你可以通过分组+字符串聚合函数实现单条书籍记录拼接所有作者名的需求，不同数据库对应的实现语法如下：

MySQL #

使用GROUP_CONCAT函数实现：

SELECT 
  books.id,
  books.title,
  GROUP_CONCAT(authors.full_name SEPARATOR ', ') AS full_name
FROM books 
INNER JOIN author_books ON author_books.book_id = books.id 
INNER JOIN authors ON authors.id = author_books.author_id 
WHERE author_books.book_id=182
GROUP BY books.id, books.title

注意：MySQL默认GROUP_CONCAT的返回长度上限为1024字节，如果作者数量较多导致返回内容被截断，可以调整group_concat_max_len参数修改上限。

PostgreSQL / SQL Server 2017+ #

使用STRING_AGG函数实现：

SELECT 
  books.id,
  books.title,
  STRING_AGG(authors.full_name, ', ') AS full_name
FROM books 
INNER JOIN author_books ON author_books.book_id = books.id 
INNER JOIN authors ON authors.id = author_books.author_id 
WHERE author_books.book_id=182
GROUP BY books.id, books.title

低版本SQL Server（2017以下） #

可以通过FOR XML PATH语法实现：

SELECT 
  b.id,
  b.title,
  STUFF((
    SELECT ', ' + a.full_name 
    FROM authors a
    INNER JOIN author_books ab ON ab.author_id = a.id
    WHERE ab.book_id = b.id
    FOR XML PATH(''), TYPE
  ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS full_name
FROM books b
WHERE b.id = 182

Oracle 11gR2+ #

使用LISTAGG函数实现：

SELECT 
  books.id,
  books.title,
  LISTAGG(authors.full_name, ', ') WITHIN GROUP (ORDER BY authors.full_name) AS full_name
FROM books 
INNER JOIN author_books ON author_books.book_id = books.id 
INNER JOIN authors ON authors.id = author_books.author_id 
WHERE author_books.book_id=182
GROUP BY books.id, books.title

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