Как объединить GROUP BY и JOIN?

Question

[FlorenceMachine]

Работаю с PhpMyAdmin. У меня есть таблица log_taking, столбцы которой выглядят так:

id | reader_id | book_id | taken_at | returned_at

Есть таблица readers:

id | last_name | first_name

(id если что разные и не совпадают)

И таблица books:

id | name | pub_date

Я хочу вытащить таблицу, в которой от каждого id из log_taking будет ровно одна строка с самой ранней датой taken_at.
(К примеру, если у меня id = 101 -- таких записей 5 штук, аналогично по 5 штук с id = 201, 301, 401, 501, то я должен вывести таблицу из 5 строк (1 строка от каждого id))

Для самой таблицы log_taking, я думаю, правильно будет сделать так:

SELECT book_id, MAX(taken_at) FROM log_taking GROUP BY book_id

Но не получается с помощью JOIN подцепить к ней еще поля first_name, last_name, pub_date и returned_at.
Помогите, пожалуйста

P.S. Я пробовал так, но это неправильно

spoiler

SELECT
log_taking.book_id,
log_taking.returned_at,
log_taking.taken_at,
readers.first_name,
readers.last_name,
readers.pub_date
FROM
log_taking JOIN
(
SELECT book_id, MAX(taken_at)
FROM log_taking
GROUP BY book_id
) books
ON log_taking.book_id = books.id
JOIN readers
ON readers.id = log_taking.reader_id

Comments

[alexalexes]

id если что разные и не совпадают

Естественно разные, в первой таблице - айдишники операций приема/возврата книг, а во второй таблице - айдишники читателей.

Я хочу вытащить таблицу, в которой от каждого id из log_taking будет ровно одна строка с самой ранней датой taken_at.

Человеческим языком: вы хотите получить список читателей + сведения о последней взятой книге?

[FlorenceMachine]

alexalexes, Да, именно так.

Answer 1

[alexalexes]

Чтобы найти читателей с последними взятыми книгами, нужно при помощи оконной функции row_number пронумеровать строки в пределах каждого читателя (задав партицию по айди читателя) в порядке убывания по дате taken_at. Из полученного результата взять строки, которые получили номера 1.

select * from
(select r.id as reader_id, r.first_name, r.last_name, b.id book_id, b.name, b.pub_date, row_number() over (partition by r.id order by l.taken_at desc) as rownum
from readers r
left join log_taking l on r.id = l.reader_id
left join books b on b.id = l.book_id
) a
where a.rownum = 1

PS: Связка left join выведет всех читателей, в независимости, брали они книги или нет. Просто join даст список читателей, которые брали книги хоть один раз.
PPS: Оконные функции доступны с MySQL версии 8.
Если версия 5, то придется городить велосипед с пользовательскими переменными.

Comments

[FlorenceMachine]

У меня вылезает ошибка #1054 - Неизвестный столбец 'l.redaer_id' в 'on clause'
Подскажите, пожалуйста, что не так делаю? Версия 8.0.19

[alexalexes]

Опечатка:
l.redaer_id -> l.reader_id

[FlorenceMachine]

alexalexes, Спасибо вам большое! Вы супер)
Долго мучился с этим

[alexalexes]

Каждая функция: count, max, min, тоже имеет оконный аналог, с ними тоже можно творить чудеса с выборкой. Читайте документацию и тренируйтесь.