在 MySQL 中使用 GROUP BY 选择最近的行

2021-12-27 00:00:00 sql group-by mysql

我正在尝试选择每个用户最近一次付款.我现在的查询选择用户第一次付款.IE.如果用户进行了两次付款并且 payment.ids 是 10 和 11，则查询将选择具有付款 ID 信息的用户，而不是 11.

I'm trying to select each user with their most recent payment. The query I have now selects the users first payment. I.e. if a user has made two payments and the payment.ids are 10 and 11, the query selects the user with the info for payment id 10, not 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 payment.id，但查询似乎忽略了它，仍然选择第一笔付款.

I've added ORDER BY payments.id, but the query seems to ignore it and still selects the first payment.

感谢所有帮助.谢谢.

推荐答案

您想要 分组最大值;本质上，将支付表分组以识别最大记录，然后将结果与自身连接以获取其他列:

You want the groupwise maximum; in essence, group the payments table to identify the maximal records, then join the result back with itself to fetch the other columns:

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 主键列.

Note that MAX(id) may not be the "most recent payment", depending on your application and schema: it's usually better to determine "most recent" based off TIMESTAMP than based off synthetic identifiers such as an AUTO_INCREMENT primary key column.

相关文章