我一直在建立一个基于Candy Crush的游戏. Score表有以下三列：
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结尾的值),则它必须返回具有最小Move数的行.
我正在尝试以下但它没有按预期工作：
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
我做错了什么？
301
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
