MySQL 为每个 Group 分组选择最新的一条记录进行排序

我们在表格中有一些数据（查看下表）。表中有 id 为主键， user_id 是用户id，balance 是用户账户余额。

这张表是所有用户的账户余额流水表，记录了每个用户账户余额变化的历史记录。

我们的需求是找到每个用户的最新的账户余额，并对其进行排序，得到账户余排名前十的用户排行，主键id是自增id，让我们将按最大的主键id获取最新记录。

样本数据

表名：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

接下来，我将通过几种不同的方式来查询得到我们想要的结果。第一种是通过 GROUP BY 和 IN，第二种是通过 GROUP BY 和 JOIN，第三种是使用 MySQL8 的 ROW_NUMBER() 窗口函数。

方法1：`GROUP BY` + `IN`

在此方法中，我们先按 user_id 进行分组，并获取最大的id（即该用户的最新流水记录的id），再通过 IN 这些 id 进行查询并排序取前10：

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

我们会先得到每个用户的最新记录的id：3, 5, 8，然后得到最终结果：

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

EXPLAIN:

id|select_type|table     |partitions|type |possible_keys|key        |key_len|ref|rows|filtered|Extra                      |
--|-----------|----------|----------|-----|-------------|-----------|-------|---|----|--------|---------------------------|
 1|PRIMARY    |coins_flow|          |ALL  |             |           |       |   |6240|   100.0|Using where; Using filesort|
 2|SUBQUERY   |coins_flow|          |range|user_id_idx  |user_id_idx|4      |   | 517|   100.0|Using index for group-by   |

方法2：`GROUP BY` + `JOIN`

这个方法与上一个方法类似，我们先按 user_id 进行分组，并获取最大的id（即该用户的最新流水记录的id），这里我们不再使用 IN，而是通过将该结果与原流水表进行关联得到每个用户的完整信息再进行排序取前10：

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

EXPLAIN:

id|select_type|table     |partitions|type  |possible_keys|key        |key_len|ref   |rows|filtered|Extra                                       |
--|-----------|----------|----------|------|-------------|-----------|-------|------|----|--------|--------------------------------------------|
 1|PRIMARY    |<derived2>|          |ALL   |             |           |       |      | 517|   100.0|Using where; Using temporary; Using filesort|
 1|PRIMARY    |cf        |          |eq_ref|PRIMARY      |PRIMARY    |4      |gcf.id|   1|   100.0|                                            |
 2|DERIVED    |coins_flow|          |range |user_id_idx  |user_id_idx|4      |      | 517|   100.0|Using index for group-by                    |

方法3：`ROW_NUMBER()`

此方法不适用于低于 8 的 MySQL 版本。因为 ROW_NUMBER() 窗口函数只在 MySQL 8（及更高版本）中可用。

使用以下查询根据 id 对每个用户（user_id）的数据进行降序排名：

SELECT *,
    ROW_NUMBER() OVER (
        PARTITION BY user_id
        ORDER BY id DESC
    ) AS rank_no
FROM coins_flow

我们得到以下数据：

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

每个用户都有一个 rank_no，rank_no=1 表示这条记录为最新记录（id最大），然后我们再通过子查询查出其中 rank_no=1 的记录进行排序取前10：

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

EXPLAIN:

id|select_type|table     |partitions|type|possible_keys|key        |key_len|ref  |rows|filtered|Extra         |
--|-----------|----------|----------|----|-------------|-----------|-------|-----|----|--------|--------------|
 1|PRIMARY    |<derived2>|          |ref |<auto_key0>  |<auto_key0>|8      |const|  10|   100.0|Using filesort|
 2|DERIVED    |coins_flow|          |ALL |             |           |       |     |6240|   100.0|Using filesort|

或者，如果您愿意，可以按照以下格式编写：

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