如何使用Postgres仅联接联接表中的一行? [英] How to join only one row in joined table with postgres?

问题描述

我有以下架构:

CREATE TABLE author (
    id   integer
  , name varchar(255)
);
CREATE TABLE book (
    id        integer
  , author_id integer
  , title     varchar(255)
  , rating    integer
);

我希望每位作者都有其最新著作:

And I want each author with its last book:

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中执行此操作:

Apparently you can do that in mysql: Join two tables in MySQL, returning just one row from the second table.

但是postgres给出了这个错误:

But postgres gives this error:

错误:列"book.id"必须出现在GROUP BY子句中或被使用 在聚合函数中:SELECT book.id，author.id，author.name， book.title为last_book，来自作者JOIN加入book book.author_id = author.id GROUP BY author.id ORDER BY book.id ASC

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列表无效 表达式以引用未分组的列(聚合中除外) 函数，因为将返回多个可能的值 用于未分组的列.

When GROUP BY is present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions, since there would be more than one possible value to return for an ungrouped column.

如何指定postgres:在连接表中按joined_table.id排序时仅给我最后一行吗?"

How can I specify to postgres: "Give me only the last row, when ordered by joined_table.id, in the joined table ?"

有了这些数据:

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"

推荐答案

select distinct on (author.id)
    book.id, author.id, author.name, book.title as last_book
from
    author
    inner join
    book on book.author_id = author.id
order by author.id, book.id desc

检查 distinct on

SELECT DISTINCT ON(expression [，...])仅保留给定表达式求值相等的每组行的第一行.使用与ORDER BY相同的规则来解释DISTINCT ON表达式(请参见上文).请注意，除非使用ORDER BY来确保所需的行首先出现，否则每个集合的第一行"都是不可预测的.

SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first.

必须在order by中包含"distinct"列，并且要与众不同.如果那不是您想要的顺序，那么您需要包装查询并重新排序

With distinct on it is necessary to include the "distinct" columns in the order by. If that is not the order you want then you need to wrap the query and reorder

select 
    *
from (
    select distinct on (author.id)
        book.id, author.id, author.name, book.title as last_book
    from
        author
        inner join
        book on book.author_id = author.id
    order by author.id, book.id desc
) authors_with_first_book
order by authors_with_first_book.name

另一种解决方案是使用Lennart的答案中的窗口函数.另一个非常通用的是

Another solution is to use a window function as in Lennart's answer. And another very generic one is this

select 
    book.id, author.id, author.name, book.title as last_book
from
    book
    inner join
    (
        select author.id as author_id, max(book.id) as book_id
        from
            author
            inner join
            book on author.id = book.author_id
        group by author.id
    ) s
    on s.book_id = book.id
    inner join
    author on book.author_id = author.id

这篇关于如何使用Postgres仅联接联接表中的一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！