如何将sql表组合成非重复的输入

Question

我想得到重复的标题，有名字，名字，而不是重复在另一个科隆。

#码

create or replace view Master_view as
select books.title, books.isbn as book_id, authors.name, authors.e_mail as author_id
from book_author join books on book_author.book_id = books.id
join authors on book_author.author_id = authors.id;

#输出

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

Answer 1

create or replace view Master_view as
select books.title, books.isbn as book_id, array_agg(authors.name), array_agg(authors.e_mail) as author_id
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);

这将给出预期的results.You必须使用isbn和书名对行进行分组。