Получение последнего вхождения строки в MySQL LEFT JOIN
У меня есть следующие таблицы в моей базе данных:
ПОЛЬЗОВАТЕЛИ
+-------+-------------------------------------+
| id | name |
+-------+-------------------------------------+
| 1 | Johnny Appleseed |
| 2 | Pete Jones |
| 3 | John Doe |
| 4 | Jane Plick |
+-------+-------------------------------------+
ДОКЛАДЫ
+-------+-------+-----------------------------+
| id | owner | title |
+-------+-------+-----------------------------+
| 1 | 1 | Weekly report #86 |
| 2 | 1 | Weekly report #87 |
| 3 | 1 | Weekly report #88 |
| 4 | 2 | Weekly report #1 |
| 5 | 3 | Weekly report #33 |
| 6 | 3 | Weekly report #34 |
+-------+-------------------------------------+
Что мне нужно сделать, так это сгруппировать результаты по имени, чтобы сам список располагался в алфавитном порядке, но мне нужно ПОСЛЕДНЕЕ вхождение строки, совпадающей с идентификатором пользователя.
Столбец «владелец» таблицы REPORTS совпадает со столбцом «id» таблицы USERS.
Мои желаемые результаты выглядят так:
Jane Plick |
John Doe | Weekly Report #34
Johnny Appleseed | Weekly Report #88
Pete Jones | Weekly Report #1
Мой текущий запрос ПОЧТИ работает, однако он показывает только ПЕРВЫЙ еженедельный отчет для этого пользователя, а не последний.
SELECT * FROM users AS a LEFT JOIN ppp_reports AS b ON a.id=b.owner WHERE a.active=1 GROUP BY a.id ORDER BY a.firstname ASC
Я перепробовал много разных вариантов, но у меня всегда остается первая строка в таблице отчетов.
Любая помощь с благодарностью.
Решение
В вашем конкретном случае, когда все отчеты называются «Еженедельный отчет №X», вы можете попробовать это:
SELECT a.id, a.name, MAX(b.title)
FROM users AS a
LEFT JOIN ppp_reports AS b ON a.id=b.owner
WHERE a.active=1
GROUP BY a.id, a.name
ORDER BY a.name ASC
Увидеть эта скрипка.
Если в отчетах могут быть другие имена, вам придется найти другой способ отличить последний отчет от других.
Другие решения
Если предположить, что ваш идентификатор отчета пронумерован по дате, то есть идентификатор отчета 2 всегда после идентификатора отчета 1, эти два запроса должны работать:
select t.id, t.name, r.title from
(select u.id, u.name, max(r.id) as report_id
from users u
left join reports r on r.owner = u.id
group by u.id, u.name) as t
left join reports r on t.report_id = r.id;
ИЛИ ЖЕ
select u.id, u.name, r.title from
users u
left join
(select r.owner, max(r.id) as report_id
from reports r
group by r.owner) as latest_report on u.id = latest_report.owner
left join reports r on latest_report.report_id = r.id;
Идея состоит в том, что вам нужно определить правильный отчет по идентификатору пользователя, а затем присоединить таблицу отчетов к ней, чтобы получить правильное имя для отчета.