如何合并SQL表实现无重复输入，合并重复书名的作者信息

当前视图会重复输出同一本书的多条记录（每位作者对应一条），要实现书名仅显示一次、对应作者信息合并为逗号分隔格式的需求，可使用PostgreSQL的string_agg聚合函数修改视图创建语句：

create or replace view Master_view as
select 
    books.title, 
    books.isbn as book_id,
    string_agg(authors.name, ', ' order by authors.name) as author_names,
    string_agg(authors.e_mail, ', ' order by authors.name) as author_emails
from book_author 
join books on book_author.book_id = books.id
join authors on book_author.author_id = authors.id
group by books.title, books.isbn;

说明 #

• string_agg(column, separator)：将分组内指定列的所有值拼接成一个字符串，用指定分隔符分隔
• order by authors.name：保证作者姓名和邮箱的拼接顺序一致，避免输出结果混乱
• group by books.title, books.isbn：按书名和ISBN分组，确保同一本书只生成一条记录

预期输出 #

title               | book_id |               author_names               |                          author_emails                          
-----------------------------------+---------+------------------------------------------+-----------------------------------------------------------------
 Agile Web Development with Rails  |  999996 | Dave Thomas, David Heinemeier Hansson, Sam Ruby | davethomas@pragprogrammers.com, dhh@railsrules.co, samruby@pragprogrammers.com
 Pragmatic Thinking and Learning   |  999998 | Andrew Hunt                               | andyhunt@pragprogrammers.com
 Pragmatic Unit Testing            |  999997 | Andrew Hunt, Dave Thomas                  | andyhunt@pragprogrammers.com, davethomas@pragprogrammers.com
 The Pragmatic Programmer          |  999999 | Andrew Hunt, Dave Thomas                  | andyhunt@pragprogrammers.com, davethomas@pragprogrammers.com
(4 rows)

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