我有以下模式:
CREATE TABLE author (
id integer
, name varchar(255)
);
CREATE TABLE book (
id integer
, author_id integer
, title varchar(255)
, rating integer
);
我希望每个作者都有他们最新的一本书:
SELECT book.id, author.id, author.name, book.title as last_book
FROM author
JOIN book book ON book.author_id = author.id
GROUP BY author.id
ORDER BY book.id ASC
显然你可以在mysql中这样做:Join two tables in MySQL, returning just one row from the second table,但是PostgreSQL会报错:
ERROR: column "book.id" must appear in the GROUP BY clause or be used in an aggregate function: SELECT book.id, author.id, author.name, book.title as last_book FROM author JOIN book book ON book.author_id = author.id GROUP BY author.id ORDER BY book.id ASC
原因如下:
当存在GROUP BY时,SELECT语句所选的列必须出现在GROUP BY子句中,或者在聚合函数中使用,否则它将有多个可能的返回值。
我该如何让PostgreSQL这样做:"只给我连接表中按joined_table.id
排序后的最后一行"?
编辑: 使用这个数据:
INSERT INTO author (id, name) VALUES
(1, 'Bob')
, (2, 'David')
, (3, 'John');
INSERT INTO book (id, author_id, title, rating) VALUES
(1, 1, '1st book from bob', 5)
, (2, 1, '2nd book from bob', 6)
, (3, 1, '3rd book from bob', 7)
, (4, 2, '1st book from David', 6)
, (5, 2, '2nd book from David', 6);
我应该看到:
book_id author_id name last_book
3 1 "Bob" "3rd book from bob"
5 2 "David" "2nd book from David"
distinct on
有点针对Postgres的特定性。如果有其他方法,我很乐意了解。 - Benjamin Crouzierdistinct on
。但不要猜测。使用explain analyze
检查两个解决方案。在我的答案中有新的解决方案。 - Clodoaldo Neto