Как присоединиться только к одной строке в объединенной таблице с postgres?

У меня есть следующая схема:

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 автор 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"

Ответ 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 (выражение [,...]) сохраняет только первую строку каждого набора строк, где эти выражения оцениваются равными. Выражения DISTINCT ON интерпретируются с использованием тех же правил, что и для ORDER BY (см. Выше). Обратите внимание, что "первая строка" каждого набора непредсказуема, если только 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

Другим решением является использование оконной функции, как в ответе Леннарта. И еще один очень общий - это

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

Ответ 2

Это может выглядеть архаично и чрезмерно просто, но это не зависит от оконных функций, CTE и агрегации подзапросов. В большинстве случаев это также самый быстрый.

SELECT bk.id, au.id, au.name, bk.title as last_book
FROM author au
JOIN book bk ON bk.author_id = au.id
WHERE NOT EXISTS (
    SELECT *
    FROM book nx
    WHERE nx.author_id = bk.author_id
    AND nx.book_id > bk.book_id
    )
ORDER BY book.id ASC
    ;

Ответ 3

Вот один из способов:

SELECT book_id, author_id, author_name, last_book
FROM (
    SELECT b.id as book_id
         , a.id as author_id
         , a.name as author_name
         , b.title as last_book
         , row_number() over (partition by a.id
                              order by b.id desc) as rn
    FROM author a
    JOIN book b 
        ON b.author_id = a.id
) last_books
WHERE rn = 1;

Ответ 4

Как небольшое отклонение от предложения @wildplasser, которое все еще работает во всех реализациях, вы можете использовать max, а не быть. Это лучше, если вы хотите, чтобы короткие соединения лучше, чем длинные, где пункты

select * 
  from author au
  join (
    select max(id) as max_id, author_id
      from book bk
     group by author_id) as lb 
    on lb.author_id = au.id
  join bk 
    on bk.id = lb.max_id;

или, чтобы дать имя подзапросу, который проясняет все, перейдите к WITH

with last_book as 
   (select max(id) as max_id, author_id
      from book bk
     group by author_id)

select * 
  from author au
  join last_book lb
    on au.id = lb.author_id
  join bk 
    on bk.id = lb.max_id;