从一列获取 MAX 值，从另一列获取 MIN [英] Get MAX value from one column and MIN from another column

问题描述

我一直在开发基于 Candy Crush 的游戏.Score 表包含三个以下列:

I've been building a game based on Candy Crush. The Score table has the three following columns:

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 具有相同的 Value(以 53 结尾)，则它必须返回 Moves 次数最少的行.

I need to get the top Score grouped by stage_level_id. If an stage_level_id have the same Value (as the one ending with 53), it must return the row with the smallest number of Moves.

我正在尝试以下操作，但没有按预期工作:

I'm trying the following but it's not working as expected:

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

我做错了什么?

推荐答案

您的尝试并不遥远.但是，您遗漏了第一个 JOIN ... ON 子句的必要部分，第二个 JOIN 不是必需的.

Your attempt wasn't that far off. You were missing a necessary part of the first JOIN ... ON clause though, and the second JOIN isn't necessary.

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

演示

这篇关于从一列获取 MAX 值，从另一列获取 MIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！