MySQL分组查询最新记录：3种高效方法详解

在许多应用场景中，尤其是财务系统或用户行为分析，获取每个用户的最新数据记录并进行排序是一个常见需求。假设我们有一张用户账户余额流水表，记录了每个用户账户余额的变化历史。本文将介绍如何使用 MySQL 查询最新的账户余额并进行排序。

样本数据

表名：coins_flow

表数据如下：

id	user_id	balance
1	111	1000
2	111	1200
3	111	1500
4	222	1100
5	222	1200
6	333	1200
7	333	1300
8	333	1600

需求分析

如何在 MySQL 中按用户分组选择最新记录并排序？

我们希望从这张表中找到每个用户的最新账户余额，并对这些余额进行排序，以获取余额排名前十的用户。通过这个示例，我们不仅可以了解 MySQL 的基本查询能力，还能运用多种方法实现这一目标。

MySQL 获取分组最新数据的 3 种方法

我们将介绍三种方法来获取所需结果：GROUP BY + IN，GROUP BY + JOIN，以及使用 MySQL 8 的窗口函数 ROW_NUMBER()。这三种方法在性能和复杂性上有所不同，因此用户可以根据自己的需求选择合适的方法。

方法 1：使用 `GROUP BY` 和 `IN`

在此方法中，我们首先按 user_id 进行分组，获取每个用户的最新流水记录的 id，然后通过 IN 子句查询并排序前十条记录：

SELECT * FROM coins_flow WHERE id IN (
    SELECT MAX(id)
    FROM coins_flow
    GROUP BY user_id
)
ORDER BY balance DESC LIMIT 10;

在执行这个查询后，我们将得到最新记录的 id 列表，并对这些记录按照 balance 排序，最终结果如下：

id	user_id	balance
8	333	1600
3	111	1500
5	222	1200

方法 2：使用 `GROUP BY` 和 `JOIN`

这种方法与第一种类似，但我们通过 JOIN 来获得完整的用户记录，而不是使用 IN：

SELECT cf.*
FROM coins_flow cf
INNER JOIN (
    SELECT MAX(id) AS id
    FROM coins_flow
    GROUP BY user_id
) gcf ON cf.id = gcf.id
ORDER BY cf.balance DESC
LIMIT 10;

这种方法能够更清晰地表达我们的意图，并能直接在主查询中获得用户的完整信息。

方法 3：使用 `ROW_NUMBER()`

如果你使用的是 MySQL 8 或更高版本，可以使用窗口函数 ROW_NUMBER()。该方法将数据按 user_id 进行分区，并按 id 降序排列：

WITH ranked_coins_flow AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY id DESC
        ) AS rank_no
    FROM coins_flow
)
SELECT * FROM ranked_coins_flow
WHERE rank_no = 1
ORDER BY balance DESC
LIMIT 10;

在这个查询中，我们首先为每个用户的记录分配了一个排名。通过在外部查询中筛选 rank_no = 1，我们可以获取每个用户的最新记录，并按照 balance 排序。

小结

通过上述三种方法，我们能够轻松获取每个用户的最新账户余额并进行排序。这在很多实际应用中都非常有用，例如对用户活动的分析、账户余额监控等。

常见问题解答（FAQ）

为什么选择最新记录的 id 作为判断标准？ 最新记录的 id 通常是自增的，这意味着 id 的最大值对应着最新的记录。
哪种方法性能更好？ 这取决于具体情况。JOIN 方法通常在数据量较大时表现更好，而窗口函数在 MySQL 8 中性能更优。
如何处理没有余额变化的用户？ 在使用 GROUP BY 和 JOIN 的方法中，这种用户的记录将不会被返回。可以通过 LEFT JOIN 保留这些用户。