MySQL从包含多次付款的用户列表的表中选择最新付款

Question

我有一张满是用户付款的表。每个用户可以多次付款。我希望为每个用户选择最新的付款。

SELECT DATE_FORMAT(FROM_UNIXTIME(MAX(p.date)), "%D %M %Y") as last_payment, p.user_id, p.amount, p.period, p.txn_id, u.name 
FROM payments as p
INNER JOIN users as u
ON u.id = p.user_id
GROUP BY p.user_id
ORDER BY p.date DESC

现在这似乎工作正常，last_ payment字段确实是用户最近付款的日期，唯一的问题是其他付款字段，如p.txn_id和 p.amount 不一定对应于用户上次付款的同一行，而是来自之前的付款。

如何确保当我选择 MAX(p.date) 时，它也会选择同一行中的其他字段。

感谢您的帮助。

Answer 1

您正在寻找groupwise maximum ;最常见的方法是将付款表连接回分组结果:

SELECT   DATE_FORMAT(FROM_UNIXTIME(p.date), '%D %M %Y') AS last_payment,
         p.user_id, p.amount, p.period, p.txn_id, u.name
FROM     payments AS p NATURAL JOIN (
           SELECT   user_id, MAX(date) date
           FROM     payments
           GROUP BY user_id
         ) t JOIN users AS u ON u.id = p.user_id
ORDER BY p.date DESC

请注意，您的原始查询完全有效(并且没有抛出错误)的唯一原因是 MySQL's non-standard extensions to GROUP BY :

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.