从一列获取最大值，从另一列获取最小值
从一列获取最大值，从另一列获取最小值
提问于 2014-07-16 04:42:52
我一直在开发一个基于糖果粉碎传奇的游戏。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具有相同的Value (与以53结尾的行相同)，则它必须返回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我哪里做错了？
回答 4
Stack Overflow用户
回答已采纳
发布于 2014-07-16 05:33:14
你的尝试也不算太远。你遗漏了第一个连接的必要部分...ON子句，第二个JOIN不是必需的。
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_idStack Overflow用户
发布于 2014-07-16 05:58:55
您可以使用NOT EXISTS获取每个(stage_id，最大分数)组的最小移动次数
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;not exists部件将结果限制为每个组中得分最高的那些行(换句话说，该组中不存在得分更高的其他行)。
此查询可以利用(stage_id,value)上的复合索引
Stack Overflow用户
发布于 2018-07-15 06:35:13
事实上，这可以通过一个带有left join和group by的select简单地完成
select s1.stage_level_id, s1.score, min(s1.moves) as moves
from scores s1
left join scores s2
on s2.stage_level_id = s1.stage_level_id
and s2.score > s1.score
where s2.score IS NULL
group by s1.stage_level_id;left join提供了：
| s1 | s2 |
|--------------------------------------|-------|-------|--------------------------------------|--------|--------|
| stage_level_id | score | moves | stage_level_id | score | moves |
| 9f7678f0-fc8f-11e3-a398-b2227cce2b53 | 35000 | 350 | (null) | (null) | (null) |
| 9f7678f0-fc8f-11e3-a398-b2227cce2b53 | 35000 | 500 | (null) | (null) | (null) |
| 9f7678f0-fc8f-11e3-a398-b2227cce2b54 | 15000 | 125 | (null) | (null) | (null) |
| 9f7678f0-fc8f-11e3-a398-b2227cce2b54 | 13500 | 100 | 9f7678f0-fc8f-11e3-a398-b2227cce2b54 | 15000 | 125 |
| 9f7678f0-fc8f-11e3-a398-b2227cce2b55 | 7500 | 25 | 9f7678f0-fc8f-11e3-a398-b2227cce2b55 | 12500 | 350 |
| 9f7678f0-fc8f-11e3-a398-b2227cce2b55 | 12500 | 350 | (null) | (null) | (null) |where子句选择每个stage_level_id包含最高分数的行
| s1 | s2 |
|--------------------------------------|-------|-------|----------------|--------|--------|
| stage_level_id | score | moves | stage_level_id | score | moves |
| 9f7678f0-fc8f-11e3-a398-b2227cce2b53 | 35000 | 350 | (null) | (null) | (null) |
| 9f7678f0-fc8f-11e3-a398-b2227cce2b53 | 35000 | 500 | (null) | (null) | (null) |
| 9f7678f0-fc8f-11e3-a398-b2227cce2b54 | 15000 | 125 | (null) | (null) | (null) |
| 9f7678f0-fc8f-11e3-a398-b2227cce2b55 | 12500 | 350 | (null) | (null) | (null) |因此，我们可以在group by制定的每个stage_level_id组上运行min(s1.moves)，以获得最终结果：
| s1 | s2 |
|--------------------------------------|-------|-------|----------------|--------|--------|
| stage_level_id | score | moves | stage_level_id | score | moves |
| 9f7678f0-fc8f-11e3-a398-b2227cce2b53 | 35000 | 350 | (null) | (null) | (null) |
| 9f7678f0-fc8f-11e3-a398-b2227cce2b54 | 15000 | 125 | (null) | (null) | (null) |
| 9f7678f0-fc8f-11e3-a398-b2227cce2b55 | 12500 | 350 | (null) | (null) | (null) |或
| stage_level_id | score | moves |
|--------------------------------------|-------|-------|
| 9f7678f0-fc8f-11e3-a398-b2227cce2b53 | 35000 | 350 |
| 9f7678f0-fc8f-11e3-a398-b2227cce2b54 | 15000 | 125 |
| 9f7678f0-fc8f-11e3-a398-b2227cce2b55 | 12500 | 350 |页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/24767814
复制相关文章
相似问题