MySQL中GROUP BY函数异常咨询：聚合字段不匹配最新记录

我来帮你拆解这个问题——这其实是MySQL中GROUP BY的一个常见“陷阱”，和它的分组模式直接相关。

表结构与数据 #

+----+-------------+--------------+----------------------------+------------+--------+
| id | Qty_holding | Qty_reserved | created                    | tokenid_id | uid_id |
+----+-------------+--------------+----------------------------+------------+--------+
| 1  | 10          | 0            | 2018-01-18 10:52:14.957027 | 1          | 1      |
| 2  | 20          | 0            | 2018-01-18 11:20:08.205006 | 8          | 1      |
| 3  | 110         | 0            | 2018-01-18 11:20:21.496318 | 14         | 1      |
| 4  | 10          | 0            | 2018-01-23 14:26:49.124607 | 1          | 2      |
| 5  | 3           | 0            | 2018-01-23 15:00:26.876623 | 11         | 2      |
| 6  | 7           | 0            | 2018-01-23 15:08:41.887240 | 11         | 2      |
| 7  | 11          | 0            | 2018-01-23 15:22:48.424224 | 11         | 2      |
| 8  | 15          | 0            | 2018-01-23 15:24:03.419907 | 11         | 2      |
| 9  | 19          | 0            | 2018-01-23 15:24:26.531141 | 11         | 2      |
| 10 | 23          | 0            | 2018-01-23 15:27:11.549538 | 11         | 2      |
| 11 | 27          | 0            | 2018-01-23 15:27:24.162944 | 11         | 2      |
| 12 | 7.7909428   | 0.11459088   | 2018-01-23 15:27:24.168643 | 1          | 2      |
| 13 | 3           | 0            | 2018-01-23 15:36:51.412340 | 14         | 2      |
| 14 | 7.5585988   | 0.11459088   | 2018-01-23 15:36:51.417177 | 1          | 2      |
| 15 | 6           | 0            | 2018-01-24 08:43:46.635069 | 14         | 2      |
| 16 | 7.3262548   | 0.11459088   | 2018-01-24 08:43:46.639984 | 1          | 2      |
| 17 | 9           | 0            | 2018-01-24 10:09:08.207816 | 14         | 2      |
| 18 | 7.0939108   | 0.11459088   | 2018-01-24 10:09:08.212842 | 1          | 2      |
| 19 | 6           | 3            | 2018-01-24 13:43:08.929586 | 14         | 2      |
| 20 | 3           | 6            | 2018-01-24 14:49:56.960112 | 14         | 2      |
| 21 | 0           | 9            | 2018-01-24 14:50:33.423671 | 14         | 2      |
| 22 | 30          | 9            | 2018-01-24 14:51:14.865453 | 14         | 2      |
| 23 | 4.7704708   | 0.11459088   | 2018-01-24 14:51:14.870256 | 1          | 2      |
| 24 | 27          | 12           | 2018-01-24 14:56:56.914009 | 14         | 2      |
| 25 | 24          | 15           | 2018-01-24 14:57:56.475939 | 14         | 2      |
| 26 | 21          | 15           | 2018-01-24 14:58:06.750903 | 14         | 2      |
| 27 | 18          | 15           | 2018-01-24 15:02:43.203878 | 14         | 2      |
| 28 | 4.7705074   | 0.11459088   | 2018-01-24 15:02:43.224901 | 1          | 2      |
| 29 | 24          | 0            | 2018-01-24 15:03:40.421943 | 11         | 2      |
| 30 | 4.9535074   | 0.11459088   | 2018-01-24 15:03:40.441552 | 1          | 2      |
| 31 | 1           | 0            | 2018-01-26 10:35:33.173801 | 18         | 2      |
| 32 | 10          | 15           | 2018-01-26 12:46:03.780807 | 14         | 2      |
+----+-------------+--------------+----------------------------+------------+--------+

你的查询与问题 #

查询1：获取每个用户-代币的最新记录时间 #

select uid_id , tokenid_id , max(created) as max_created 
from accounts_userholding 
group by uid_id , tokenid_id

结果是正确的，它按uid_id和tokenid_id分组，返回了每组的最新created时间：

+--------+------------+----------------------------+
| uid_id | tokenid_id | max_created                |
+--------+------------+----------------------------+
| 1      | 1          | 2018-01-18 10:52:14.957027 |
| 1      | 8          | 2018-01-18 11:20:08.205006 |
| 1      | 14         | 2018-01-18 11:20:21.496318 |
| 2      | 1          | 2018-01-24 15:03:40.441552 |
| 2      | 11         | 2018-01-24 15:03:40.421943 |
| 2      | 14         | 2018-01-26 12:46:03.780807 |
| 2      | 18         | 2018-01-26 10:35:33.173801 |
+--------+------------+----------------------------+

查询2：尝试同时获取最新记录的持有量和预留量 #

select uid_id , Qty_holding , Qty_reserved, tokenid_id , max(created) as max_created 
from accounts_userholding 
group by uid_id , tokenid_id

结果里的Qty_holding和Qty_reserved并没有对应max_created的那条记录——比如uid_id=2且tokenid_id=14时，最新记录是id=32的Qty_holding=10，但查询返回了3：

+--------+-------------+--------------+------------+----------------------------+
| uid_id | Qty_holding | Qty_reserved | tokenid_id | max_created                |
+--------+-------------+--------------+------------+----------------------------+
| 1      | 10          | 0            | 1          | 2018-01-18 10:52:14.957027 |
| 1      | 20          | 0            | 8          | 2018-01-18 11:20:08.205006 |
| 1      | 110         | 0            | 14         | 2018-01-18 11:20:21.496318 |
| 2      | 10          | 0            | 1          | 2018-01-24 15:03:40.441552 |
| 2      | 3           | 0            | 11         | 2018-01-24 15:03:40.421943 |
| 2      | 3           | 0            | 14         | 2018-01-26 12:46:03.780807 |
| 2      | 1           | 0            | 18         | 2018-01-26 10:35:33.173801 |
+--------+-------------+--------------+------------+----------------------------+

为什么会出现这个问题？ #

这是因为MySQL在禁用ONLY_FULL_GROUP_BY模式时的特殊行为：

• 标准SQL要求，当你使用GROUP BY时，SELECT列表中的列必须要么是GROUP BY子句中的分组列，要么是被聚合函数（比如MAX()、SUM()）包裹的列。
• 如果你的MySQL没有开启ONLY_FULL_GROUP_BY（这在旧版本中是默认设置），它不会报错，而是会从分组内的任意一行中选取非聚合、非分组列的值——这个值完全是随机的（或者说取决于存储引擎的行读取顺序），并不一定和MAX(created)对应的那一行匹配。

你看到的Qty_holding=3，只是分组uid_id=2, tokenid_id=14中某一行的值，刚好不是最新的那一条。

正确的解决方法 #

要获取每个用户-代币最新记录的完整数据，有两种常用方案：

方案1：子查询关联（兼容所有MySQL版本） #

先通过子查询找到每组的最新时间，再关联原表获取对应行的所有字段：

SELECT a.uid_id, a.Qty_holding, a.Qty_reserved, a.tokenid_id, a.created AS max_created
FROM accounts_userholding a
INNER JOIN (
    SELECT uid_id, tokenid_id, MAX(created) AS max_created
    FROM accounts_userholding
    GROUP BY uid_id, tokenid_id
) b ON a.uid_id = b.uid_id 
   AND a.tokenid_id = b.tokenid_id 
   AND a.created = b.max_created;

方案2：窗口函数（MySQL 8.0+推荐） #

使用ROW_NUMBER()窗口函数给每组内的记录按时间排序，取排名第一的行：

SELECT uid_id, Qty_holding, Qty_reserved, tokenid_id, created AS max_created
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY uid_id, tokenid_id ORDER BY created DESC) AS rn
    FROM accounts_userholding
) t
WHERE rn = 1;

这两种方法都能准确返回每个用户-代币最新记录的Qty_holding和Qty_reserved值，解决你遇到的问题。

内容的提问来源于stack exchange，提问作者Manish Kumar