时间戳

首页 > 解决方案 > 使用 GROUP BY 订购时的行值错误

mysql - 使用 GROUP BY 订购时的行值错误

问题描述

我有一个表（10k 条记录），其中包含比赛历史 beetwen 2 名球员，如下所示：

+----+---------+--------+-------------------+--------------------+------------+-----------+--+
| id | Winner  | Losser | Winner_new_rating | Losser_new_rating  | (datetime) | matchname |  |
+----+---------+--------+-------------------+--------------------+------------+-----------+--+
|9999| Peter   | Wally  |           1200.34 |            1000.23 | 11.11.2013 | string    |  |
|9998| Peter   | Sally  |           1190.23 |            1123.23 | 10.01.2013 | string    |  |
|9997| Logan   | Demo   |            999.24 |            1222.23 | 11.11.2012 | string    |  |
|9996| Hex     | Wally  |            750.34 |            1333.23 | 14.07.2012 | string    |  |
|9995| Peter   | Sally  |           1180.45 |            1444.23 | 12.11.2011 | string    |  |
|9994| Wayne   | Wally  |           1450.22 |            1555.23 | 11.05.2011 | string    |  |
+----+---------+--------+-------------------+--------------------+------------+-----------+--+

我试图从中获得按玩家分组的历史最高峰。不知何故，当我这样做时：

SELECT Winner, Losser, MAX(Winner_new_rating) AS maxrating, datetime, matchname
FROM
       ( SELECT * 
         FROM gamelist
         ORDER BY Winner_new_rating
       ) AS innerTable
GROUP BY Winner
ORDER BY maxrating DESC
LIMIT 100

除了 datetime 和 matchname 似乎取自随机记录外，我得到了几乎很好的输出。

因为我想像历史最高峰的排名一样输出，所以我需要正确的比赛日期和比赛名称（实际上是比赛名称），以显示该球员何时达到他的最佳高峰。

例如我需要什么：

Greg  / 1455.99 / date of this game when he reached his max rating / matchname
Peter / 1234.23 / date of this game when he reached his max rating / matchname
Wally / 1199.22 / date of this game when he reached his max rating / matchname
Sally / 1173.11 / date of this game when he reached his max rating / matchname
Demon / 1011.22 / date of this game when he reached his max rating / matchname
Omen  /  999.13 / date of this game when he reached his max rating / matchname

...

当然在 datetime 列中是 datetime 格式，这只是示例。

不知道如何做得更好。先感谢您。

标签： mysql

解决方案

您正在使用聚合函数，其中 select 中的列未在 group by 中提及 .. 这仅适用于 5.7 之前的 mysql 版本，但会产生不可预测的结果

为避免这种情况，您应该加入聚合结果 

SELECT a.Winner, a.Losser, t.max_rate, a.datetime, a.matchname
FROM gamelist
INNER JOIN  ( 
    SELECT Winner, MAX(Winner_new_rating) max_rate
    FROM gamelist
    GROUP BY Winner
    ORDER BY Winner_new_rating DESC
    LIMIT 100
) t on t.max_rate = a.gamelist
      AND t.Winner  = a.Winner
ORDER BY maxrating DESC

推荐阅读