Вывод данных из двух таблиц с использованием самой последней записи
Я проделал хороший поиск похожего вопроса, но без радости. Я думаю, что ответ на мой вопрос основан на DISTINCT, GROUP и UNION, но просто не может соединить все вместе каким-либо образом, который имеет смысл.
У меня есть 2 таблицы:
users(id, username)
activitylog(id, userid, usernamedisp, activity)
Пример данных:
users
id, username
1, Joe Bloggs
2, Jane Doe
3, John Smith
activitylog
id, userid, usernamedisp, activity
1, 3, John Smith , logged in
2, 3, John Smith , visited about us page
3, 1, Joe Bloggs , logged in
4, 3, John Smith , logged out
5, 2, Jane Doe , logged in
6, 1, Joe Bloggs , logged out
7, 2, Jane Doe , visited about us page
(есть другая не связанная, но действительная причина, по которой имя пользователя появляется в обеих таблицах)
Я хочу, чтобы выходные данные выводили список пользователей в порядке users.id вместе с activitylog.username и, наконец, только самой последней активностьlog.activity, которая должна выглядеть следующим образом:
User, Username , Last Activity
1, Joe Bloggs, logged out
2, Jane Doe , visited about us page
3, John Smith, logged out
Хотя я мог бы сделать это, выполнив запрос внутри цикла для каждого результата (операция SELECT из Activitylog, где userid = $ foo ORDER BY id DESC, LIMIT 0,1) — я бы предпочел выполнить все это в одном хорошем чистом куске кода так как количество пользователей может быть между 50-100, и это слишком много запросов для моего удобства.
Я уверен, что это возможно, но я просто не могу соединить это.
Решение
Вам нужен запрос, состоящий из двух частей: один, чтобы получить список идентификаторов пользователей и их «максимальный» идентификатор, который будет их последним действием. Затем запрос контейнера, который берет эти идентификаторы и получает остальную информацию:
SELECT activitylog.userid, users.username, activitylog.activity
FROM activitylog
LEFT JOIN users ON activitylog.userid = users.id
WHERE activitylog.id IN (
SELECT MAX(id)
FROM activitylog
GROUP BY userid
)
Другие решения
Продолжение. из обсуждения в комментариях …
Этот запрос должен дать вам следующий набор решений, который вы ищете.
SELECT AL.userid
,users.username
,AL.activity
FROM users
LEFT JOIN (
SELECT activitylog.userid
,activitylog.activity
FROM activitylog
WHERE activitylog.id IN (
SELECT MAX(id)
FROM activitylog
GROUP BY userid
)
) as AL ON AL.userid = users.id