MySQL LEFT JOIN с использованием MAX & GROUP BY на объединенной таблице?

У меня есть две таблицы (участники и действия), и я пытаюсь запросить членов с последней активностью для каждого участника. У меня есть работа с двумя запросами (один для получения членов и второй с max (id) и group by (member) в действиях) и некоторый код для объединения данных. Я уверен, что это может быть сделано с помощью одного запроса, но я не могу его полностью решить. Любые идеи?

таблица участников

id, name
 1, Shawn
 2, bob
 3, tom

таблица действий

id, member_id, code, timestamp, description
 1,         1,  123,     15000, baked a cake
 2,         1,  456,     20000, ate dinner
 3,         2,  789,     21000, drove home
 4,         1,  012,     22000, ate dessert

желаемый результат:

id, name,  activity_code, activity_timestamp, activity_description
 1, shawn, 012,           22000,              ate dessert
 2, bob,   789,           21000,              drove home
 3, tom,   null,          null,               null

Ответ 1

Проблема "последняя для каждой группы" чрезвычайно распространена в SQL. Существует множество примеров решений этой самой проблемы только на этом сайте.

Если ваши временные метки являются uniqe для активности каждого члена:

SELECT
  m.id,
  m.name,
  a.code activity_code,
  a.timestamp activity_timestamp,
  a.description activity_description
FROM
  members m
  INNER JOIN activities a ON a.member_id = m.id
WHERE
  a.timestamp = (SELECT MAX(timestamp) FROM activities WHERE member_id = m.id)

в качестве альтернативы, если ваш идентификатор активности увеличивается монотонно со временем:

  ...
WHERE
  a.id = (SELECT MAX(id) FROM activities WHERE member_id = m.id)

Вам не нужно группировать. Но запрос будет полезен из индекса на activities над (member_id, timestamp) или (member_id, id), соответственно.

ИЗМЕНИТЬ

Чтобы показать участников, которые не зарегистрировали действие, используйте левое соединение, подобное этому.

SELECT
  m.id,
  m.name,
  a.code activity_code,
  a.timestamp activity_timestamp,
  a.description activity_description
FROM
  members m
  LEFT JOIN activities a ON 
    a.member_id = m.id
    AND a.timestamp = (SELECT MAX(timestamp) FROM activities WHERE member_id = m.id)

Обратите внимание, что нет предложения WHERE. Семантически, WHERE применяется после завершения объединений. Таким образом, предложение WHERE будет удалять строки, добавленные в LEFT JOIN, что дает тот же результат, что и исходный INNER JOIN.

Но если вы примените дополнительное предикатное право в условии соединения, LEFT JOIN будет работать как ожидалось.

Ответ 2

SELECT 
    members.id ,
    members.name,
    activities.code AS activity_code,
    activities.timestamp AS activity_timestamp,
    activities.description AS activity_description
FROM 
    members
    LEFT JOIN activities
        ON members.id = activities.member_id
    LEFT JOIN 
        (
            SELECT
                activities.member_id
                MAX(activities.id) AS id
            FROM activities
            GROUP BY 
                activities.member_id
        ) AS t1
        ON activities.id = t1.id
WHERE
    t1.id IS NOT NULL

Ответ 3

Select max(a.id), m.name, a.activity_code, a.activity_timestamp, a.activity_description
From members m
     Left join
     activities a on a.member_id=m.id
Group by  m.name, a.activity_code, a.activity_timestamp, a.activity_description