MySQL Max错误的字段

Question

执行以下查询

SELECT `game_turns`.`in_darts`, `game_turns`.`date`, MAX(game_turns.score) AS max_score
FROM `game_turns`
JOIN `games` ON `games`.`id` = `game_turns`.`game_id` AND `games`.`training` = 1
WHERE `game_turns`.`uid` = 2
AND `game_turns`.`out` = 1
AND `game_turns`.`in_darts` = 3
ORDER BY `game_turns`.`score` DESC
LIMIT 1

我得到了该用户id (uid)的最大分数，并在3部分中输出，但其余部分(日期)是错误的。

字段为

Score   Uid     GameID      Score    out    in_darts    date
121     2       4           8        1      3           2015-07-21 13:52:12
8465    2       142         100      1      3           2015-09-05 19:46:29

从行ID 8465返回100分，但其余部分来自行ID 121

我在谷歌上搜索了一下，得到了一些Stackoverflow结果，说我应该使用ORDER BY和LIMIT 1，但看起来它对我不起作用。

按日期排序也没用

Answer 1

一个简单的order by和limit应该做你想要的:

SELECT gt.`in_darts`, gt.`date`, gt.score
FROM `game_turns` gt JOIN
     `games` g
     ON g.`id` = gt.`game_id` AND g.`training` = 1
WHERE gt.`uid` = 2 AND gt.`out` = 1 AND gt.`in_darts` = 3
ORDER BY gt.`score` DESC
LIMIT 1;

不需要聚合

Answer 2

如果寻求一个解决方案，将工作于多个UID那么聚合变得有用-通过子查询。

SQL小提琴

MySQL 5.6 Schema Setup:

CREATE TABLE Table1
    (`Score_A` int, `Uid` int, `GameID` int, `Score_B` int, `out` int, `in_darts` int, `date` datetime)
;
INSERT INTO Table1
    (`Score_A`, `Uid`, `GameID`, `Score_B`, `out`, `in_darts`, `date`)
VALUES
    (121, 2, 4, 8, 1, 3, '2015-07-21 13:52:12'),
    (8465, 2, 142, 100, 1, 3, '2015-09-05 19:46:29')
;

查询1

:

SELECT
      t.*
FROM table1 t
INNER JOIN (
  SELECT Uid, max(Score_B) as Score_B
  FROM table1
  GROUP BY uid
  ) msb ON t.Uid = msb.Uid and t.Score_B = msb.Score_B

结果

:

| Score_A | Uid | GameID | Score_B | out | in_darts |                        date |
|---------|-----|--------|---------|-----|----------|-----------------------------|
|    8465 |   2 |    142 |     100 |   1 |        3 | September, 05 2015 19:46:29 |