如何使用Postgres仅联接联接表中的一行？

小编典典

如何使用Postgres仅联接联接表中的一行？

sql

我有以下架构：

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中执行此操作：在MySQL中联接两个表，仅从第二个表返回一行。

但是postgres给出了这个错误：

错误：“ book.id”列必须出现在GROUP BY子句中或在聚合函数中使用：SELECT
book.id，author.id，author.name，book.title为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列表表达式不能引用未分组的列（聚合函数中除外），因为对于未分组的列，将有多个可能的值返回。

我如何指定postgres：“仅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"

阅读 249

2021-04-15

共1个答案

小编典典

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来确保所需的行首先出现，否则每个集合的“第一行”都是不可预测的。

带有独特的内容，有必要在中添加“与众不同”列order by。如果那不是您想要的顺序，那么您需要包装查询并重新排序

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的答案中的窗口函数。另一个非常普通的是

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

2021-04-15