mysql - 从一列获取MAX值，从另一列获取MIN

我一直在制作一个基于糖果粉碎的游戏。分数表有以下三列：

stage_level_id                        | value | moves
------------------------------------------------------
9f7678f0-fc8f-11e3-a398-b2227cce2b53  | 35000 | 350
9f7678f0-fc8f-11e3-a398-b2227cce2b53  | 35000 | 500
9f7678f0-fc8f-11e3-a398-b2227cce2b54  | 15000 | 125
9f7678f0-fc8f-11e3-a398-b2227cce2b54  | 13500 | 100
9f7678f0-fc8f-11e3-a398-b2227cce2b55  | 12500 | 350
9f7678f0-fc8f-11e3-a398-b2227cce2b55  | 7500  | 25

我需要得到按stage_level_id分组的最高分数。如果stage_level_id与以53结尾的Value具有相同的Moves，则它必须返回具有最小个数的行。
我正在尝试以下操作，但效果不如预期：

SELECT a.stage_level_id, MAX(a.value) as max_value, a.moves
FROM scores a
LEFT JOIN scores b ON (
  a.stage_level_id = b.stage_level_id
)
RIGHT JOIN scores c ON (
  c.moves = ( SELECT MIN(moves) as moves FROM scores WHERE c.stage_level_id =         a.stage_level_id )
)
WHERE a.player_id = 1475332386040815
GROUP BY a.stage_level_id

预期结果是：

stage_level_id                        | value | moves
------------------------------------------------------
9f7678f0-fc8f-11e3-a398-b2227cce2b53  | 35000 | 350
9f7678f0-fc8f-11e3-a398-b2227cce2b54  | 15000 | 125
9f7678f0-fc8f-11e3-a398-b2227cce2b55  | 12500 | 350

我做错什么了？

最佳答案

你的尝试并不遥远。你错过了第一次加入的必要部分…但是，on子句和第二个连接是不必要的。

SELECT tbl1.stage_level_id, tbl1.max_value, MIN(s.moves) AS moves
FROM
(
  SELECT stage_level_id, MAX(value) AS max_value
  FROM scores
  GROUP BY stage_level_id
) tbl1
LEFT JOIN scores s ON tbl1.stage_level_id = s.stage_level_id AND tbl1.max_value = s.value
GROUP BY stage_level_id

DEMO