从一列中获取最大值，从另一列中获取最小值

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

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.

我需要得到按stage_level_id分组的最高分数。如果stage_level_id具有相同的值(作为以53结尾的值)，它必须返回最小数量的移动的行。

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

The expected result is:

预期的结果是:

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

What I'm doing wrong?

我做错了什么吗?

2 个解决方案

#1

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

DEMO

演示

#2

You can get the minimum number of moves for each (stage_id,max score) group using NOT EXISTS

您可以获得每个(stage_id,max score)组使用不存在的最小移动数

select stage_level_id, max(value), min(moves)
from scores s1
where not exists (
    select 1 from scores s2
    where s2.stage_level_id = s1.stage_level_id
    and s2.value > s1.value
)
group by stage_level_id;

The not exists part limits the results to only those rows that have the maximum score within each group (in other words no other row with a higher score within the group exists).

不存在的部分将结果限制为只有在每个组中有最大分数的行(换句话说，在组内没有其他的行具有更高的分数)。

This query can take advantage of a composite index on (stage_id,value)

这个查询可以利用一个复合索引(stage_id,value)

http://sqlfiddle.com/#!2/88ee6/8

http://sqlfiddle.com/ ! 2/88ee6/8

#1