mysql - 获取分组依据的最新记录

Question

我有一个这样的表结构:

ID  Win     Bet    UserID       Date
1   win     1000     1      2014-11-12 19:15:34
2   win     1000     1      2014-11-12 19:15:49
3   loose   1000     1      2014-11-12 19:21:52

我运行这样的查询:

SELECT 
    u.Username,
    SUM(b.Bet) AS totalWin,
    b.date,
    b.win
FROM blackjack_history AS b
INNER JOIN users_main AS u ON b.UserID = u.ID
WHERE b.win = 'win' GROUP BY b.UserID ORDER BY totalWin DESC, LIMIT 10

问题是当我运行此查询时，它返回有关日期的 ID 1 的行。我如何返回 ID 2 的日期？我尝试过使用 Order By 但这似乎不起作用

Answer 1

您不仅仅想要最新的记录。你也在做聚合。因此，只需使用 max():

SELECT u.Username, SUM(b.Bet) AS totalWin, max(b.date) as date, b.win 
FROM blackjack_history b INNER JOIN
     users_main u
     ON b.UserID = u.ID
WHERE b.win = 'win'
GROUP BY b.UserID
ORDER BY totalWin DESC
LIMIT 10;