Выберите самую последнюю строку с GROUP BY в MySQL

Я пытаюсь выбрать каждого пользователя с их последним платежом. В запросе, который у меня есть, теперь выбирается первый платеж пользователей. То есть если пользователь сделал два платежа, а payment.id - 10 и 11, запрос выбирает пользователя с информацией для идентификатора платежа 10, а не 11.

  SELECT users.*, payments.method, payments.id AS payment_id 
    FROM `users` 
         LEFT JOIN `payments` ON users.id = payments.user_id 
GROUP BY users.id

Я добавил ORDER BY payments.id, но запрос, кажется, игнорирует его и по-прежнему выбирает первый платеж.

Вся помощь оценивается. Спасибо.

Ответ 1

Вы хотите групповой максимум; по существу, группировать таблицу платежей, чтобы идентифицировать максимальные записи, а затем присоединить результат назад к себе, чтобы получить другие столбцы:

SELECT users.*, payments.method, payments.id AS payment_id
FROM   payments NATURAL JOIN (
  SELECT   user_id, MAX(id) AS id 
  FROM     payments
  GROUP BY user_id
) t RIGHT JOIN users ON users.id = t.user_id

Обратите внимание, что MAX(id) может не быть "последним платежом" в зависимости от вашего приложения и схемы: обычно лучше определить "самый последний" на основе TIMESTAMP, чем на основе синтетических идентификаторов, таких как AUTO_INCREMENT столбца первичного ключа.

Ответ 2

Я только что имел дело с почти той же проблемой и нашел эти ответы полезными. Мое тестирование, похоже, предполагает, что вы можете сделать его несколько проще, чем принятый ответ, а именно:

SELECT u.*, p.method, p.id AS payment_id 
FROM `users` u, `payments` p
WHERE u.id = p.user_id 
    AND p.id = (SELECT MAX(p2.id) FROM payments p2
                    WHERE p2.user_id = u.id);

Я не тестировал производительность, но db, над которым я работаю, имеет более 50 000 пользователей и более 60 000 платежей, а запрос выполняется за 0,024 секунды.

Ответ 3

Мое решение:

SELECT

u.codigo, 
u.nome,  
max(r.latitude),  
max(r.longitude),  
max(r.data_criacao) 

from TAB_REGISTRO_COORDENADAS  r

inner join TAB_USUARIO u

on u.codigo = r.cd_usuario

group by u.codigo

Ответ 4

Сделав еще один шаг, мы также можем использовать:

select payment_id, cust_id, amount, payment_method 
from my_table where payment_id in 
(
    select max(payment_id) from my_table group by cust_id
);

... но этот запрос также слишком длинный в моем контексте. Внутренний выбор курит быстро, но внешний занимает некоторое время, и только 124 результата от внутреннего. Идеи?

Ответ 5

Я уже сталкивался с этим раньше. Группа больше предназначена для совокупных выражений или идентичных записей. Мои исследования показали, что лучше всего сделать что-то вроде этого:

    SELECT  u.*, p.method, p.id AS payment_id
    FROM    (
        SELECT  DISTINCT users.id
        FROM    users
        ) ur
    JOIN    payments p
    ON      p.id =
        (
        SELECT  pt.id
        FROM    payments pt
        WHERE   pt.user_id = ur.id
        ORDER BY
                pt.id DESC
        LIMIT 1
        )

Ответ 6

Я давно читал следующее решение о SO, но не могу найти ссылку на кредит, но здесь идет:

SELECT users.*, payments.method, payments.id AS payment_id, payments2.id
FROM users
JOIN payments
    ON users.id = payments.user_id 
LEFT JOIN payments2
    ON payments.user_id = payments2.user_id
    AND payments.id < payments2.id
WHERE payments2.id IS NULL

Чтобы понять, как это работает, просто отпустите WHERE payments2.id IS NULL, и вы увидите, что происходит, например, он может произвести следующий вывод (я не создал схему для проверки этого, так что это псевдовыход), Предположим, что в payments имеются следующие записи:

id | user_id | method
1  | 1       | VISA
2  | 1       | VISA
3  | 1       | VISA
4  | 1       | VISA

И вышеупомянутый SQL (без предложения WHERE payments2.id IS NULL) должен произвести:

users.id | payments.method | payments.id | payments2.id
1        | VISA            | 1           | 2
1        | VISA            | 1           | 3
1        | VISA            | 1           | 4
1        | VISA            | 2           | 3
1        | VISA            | 2           | 4
1        | VISA            | 3           | 4
1        | VISA            | 4           | NULL

Как вы можете видеть, последняя строка дает желаемый результат, а так как там нет payments2.id > 4, LEFT JOIN приводит к payments2.id = NULL.

Я нашел это решение намного быстрее (с моих ранних тестов), чем принятый ответ.

Используя другую схему, но похожий запрос, из 16095 записей:

select as1.*, as2.id
from allocation_status as1
left join allocation_status as2 
    on as1.allocation_id = as2.allocation_id
    and as1.id < as2.id
where as2.id is null;

16095 rows affected, taking 4.1ms

По сравнению с принятым ответом MAX/подзапроса:

SELECT as1.* 
FROM allocation_status as1
JOIN (
    SELECT max(id) as id
    FROM allocation_status
    group by allocation_id
) as_max on as1.id = as_max.id 

16095 rows affected, taking 14.8ms