在MySQL中选择带有GROUP BY的最近一行 [英] Select most recent row with GROUP BY in MySQL

问题描述

我尝试选择每个用户最近的付款。我现在的查询选择用户的第一次付款。即如果用户进行了两次付款，并且 payment.id s是10和11，则查询将选择支付id为10而非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

I'已添加 ORDER BY payments.id ，但该查询似乎忽略它并仍选择第一笔付款。

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

所有帮助赞赏。
Thanks。

All help appreciated. Thanks.

推荐答案

您需要群组最大值;实质上，将支付表分组以识别最大记录，然后将结果与自己结合以获取其他列：

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

请注意<$ c根据您的应用程序和架构，$ c> MAX（id）可能不是最近的付款：通常最好确定 / em>基于 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.

这篇关于在MySQL中选择带有GROUP BY的最近一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！