blocks|key|91448|text|你的尝试也不算太远。你遗漏了第一个连接的必要部分...ON子句，第二个JOIN不是必需的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|91449|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|code-block|syntax|javascript|91450|DEMO|offset|length|91451|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlfiddle.com/#!2/55a40f/25^0|0|0|0|4|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

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.

<pre><code>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
</code></pre>

<a href="http://sqlfiddle.com/#!2/55a40f/25" rel="nofollow">DEMO</a>

blocks|key|4597713|text|您可以使用NOT+EXISTS获取每个(stage_id，最大分数)组的最小移动次数|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4597714|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;|code-block|syntax|javascript|4597715|not+exists部件将结果限制为每个组中得分最高的那些行(换句话说，该组中不存在得分更高的其他行)。|4597716|此查询可以利用(stage_id,value)上的复合索引|4597717|http://sqlfiddle.com/#!2/88ee6/8|4597718|entityMap|0|LINK|mutability|MUTABLE|url^0|5|A|0|0|0|A|0|7|G|0|0|W|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@$9|Y|A|Z|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|10|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|11|8|@$9|12|A|13|B|C]]|D|@]|E|$]]|$1|M|3|N|5|6|7|14|8|@$9|15|A|16|B|C]]|D|@]|E|$]]|$1|O|3|P|5|6|7|17|8|@]|D|@$9|18|A|19|1|1A]]|E|$]]|$1|Q|3|-4|5|6|7|1B|8|@]|D|@]|E|$]]]|R|$S|$5|T|U|V|E|$W|P]]]]

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

<pre><code>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 &gt; s1.value
)
group by stage_level_id;
</code></pre>

The <code>not exists</code> 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 <code>(stage_id,value)</code>

<a href="http://sqlfiddle.com/#!2/88ee6/8" rel="nofollow">http://sqlfiddle.com/#!2/88ee6/8</a>

blocks|key|4597780|text|事实上，这可以通过一个带有left+join和group+by的select简单地完成|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4597781|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;|code-block|syntax|javascript|4597782|left+join提供了：|4597783|%7C+s1+++++++++++++++++++++++++++++++++++++++++++++++++++%7C+s2+++++++++++++++++++++++++++++++++++++++++++++++++++++%7C
%7C--------------------------------------%7C-------%7C-------%7C--------------------------------------%7C--------%7C--------%7C
%7C+stage_level_id+++++++++++++++++++++++%7C+score+%7C+moves+%7C+stage_level_id+++++++++++++++++++++++%7C+score++%7C+moves++%7C
%7C+9f7678f0-fc8f-11e3-a398-b2227cce2b53+%7C+35000+%7C+350+++%7C+(null)+++++++++++++++++++++++++++++++%7C+(null)+%7C+(null)+%7C
%7C+9f7678f0-fc8f-11e3-a398-b2227cce2b53+%7C+35000+%7C+500+++%7C+(null)+++++++++++++++++++++++++++++++%7C+(null)+%7C+(null)+%7C
%7C+9f7678f0-fc8f-11e3-a398-b2227cce2b54+%7C+15000+%7C+125+++%7C+(null)+++++++++++++++++++++++++++++++%7C+(null)+%7C+(null)+%7C
%7C+9f7678f0-fc8f-11e3-a398-b2227cce2b54+%7C+13500+%7C+100+++%7C+9f7678f0-fc8f-11e3-a398-b2227cce2b54+%7C+15000++%7C+125++++%7C
%7C+9f7678f0-fc8f-11e3-a398-b2227cce2b55+%7C+7500++%7C+25++++%7C+9f7678f0-fc8f-11e3-a398-b2227cce2b55+%7C+12500++%7C+350++++%7C
%7C+9f7678f0-fc8f-11e3-a398-b2227cce2b55+%7C+12500+%7C+350+++%7C+(null)+++++++++++++++++++++++++++++++%7C+(null)+%7C+(null)+%7C|4597784|where子句选择每个stage_level_id包含最高分数的行|4597785|%7C+s1+++++++++++++++++++++++++++++++++++++++++++++++++++%7C+s2+++++++++++++++++++++++++++++++%7C
%7C--------------------------------------%7C-------%7C-------%7C----------------%7C--------%7C--------%7C
%7C+stage_level_id+++++++++++++++++++++++%7C+score+%7C+moves+%7C+stage_level_id+%7C+score++%7C+moves++%7C
%7C+9f7678f0-fc8f-11e3-a398-b2227cce2b53+%7C+35000+%7C+350+++%7C+(null)+++++++++%7C+(null)+%7C+(null)+%7C
%7C+9f7678f0-fc8f-11e3-a398-b2227cce2b53+%7C+35000+%7C+500+++%7C+(null)+++++++++%7C+(null)+%7C+(null)+%7C
%7C+9f7678f0-fc8f-11e3-a398-b2227cce2b54+%7C+15000+%7C+125+++%7C+(null)+++++++++%7C+(null)+%7C+(null)+%7C
%7C+9f7678f0-fc8f-11e3-a398-b2227cce2b55+%7C+12500+%7C+350+++%7C+(null)+++++++++%7C+(null)+%7C+(null)+%7C|4597786|因此，我们可以在group+by制定的每个stage_level_id组上运行min(s1.moves)，以获得最终结果：|4597787|%7C+s1+++++++++++++++++++++++++++++++++++++++++++++++++++%7C+s2+++++++++++++++++++++++++++++++%7C
%7C--------------------------------------%7C-------%7C-------%7C----------------%7C--------%7C--------%7C
%7C+stage_level_id+++++++++++++++++++++++%7C+score+%7C+moves+%7C+stage_level_id+%7C+score++%7C+moves++%7C
%7C+9f7678f0-fc8f-11e3-a398-b2227cce2b53+%7C+35000+%7C+350+++%7C+(null)+++++++++%7C+(null)+%7C+(null)+%7C
%7C+9f7678f0-fc8f-11e3-a398-b2227cce2b54+%7C+15000+%7C+125+++%7C+(null)+++++++++%7C+(null)+%7C+(null)+%7C
%7C+9f7678f0-fc8f-11e3-a398-b2227cce2b55+%7C+12500+%7C+350+++%7C+(null)+++++++++%7C+(null)+%7C+(null)+%7C|4597788|或|4597789|%7C+stage_level_id+++++++++++++++++++++++%7C+score+%7C+moves+%7C
%7C--------------------------------------%7C-------%7C-------%7C
%7C+9f7678f0-fc8f-11e3-a398-b2227cce2b53+%7C+35000+%7C+350+++%7C
%7C+9f7678f0-fc8f-11e3-a398-b2227cce2b54+%7C+15000+%7C+125+++%7C
%7C+9f7678f0-fc8f-11e3-a398-b2227cce2b55+%7C+12500+%7C+350+++%7C|4597790|在http://sqlfiddle.com/#!9/50ed8/6/0上进行测试。|4597791|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlfiddle.com/#!9/50ed8/6/0^0|D|9|N|8|W|6|0|0|0|9|0|0|0|5|B|E|0|0|8|8|L|E|13|D|0|0|0|0|1|Y|0|0^^$0|@$1|2|3|4|5|6|7|1A|8|@$9|1B|A|1C|B|C]|$9|1D|A|1E|B|C]|$9|1F|A|1G|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|1H|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|1I|8|@$9|1J|A|1K|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|1L|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|1M|8|@$9|1N|A|1O|B|C]|$9|1P|A|1Q|B|C]]|D|@]|E|$]]|$1|Q|3|R|5|H|7|1R|8|@]|D|@]|E|$I|J]]|$1|S|3|T|5|6|7|1S|8|@$9|1T|A|1U|B|C]|$9|1V|A|1W|B|C]|$9|1X|A|1Y|B|C]]|D|@]|E|$]]|$1|U|3|V|5|H|7|1Z|8|@]|D|@]|E|$I|J]]|$1|W|3|X|5|6|7|20|8|@]|D|@]|E|$]]|$1|Y|3|Z|5|H|7|21|8|@]|D|@]|E|$I|J]]|$1|10|3|11|5|6|7|22|8|@]|D|@$9|23|A|24|1|25]]|E|$]]|$1|12|3|-4|5|6|7|26|8|@]|D|@]|E|$]]]|13|$14|$5|15|16|17|E|$18|19]]]]

In fact, this can be done simply by one <code>select</code> with a <code>left join</code> and a <code>group by</code>:

<pre><code>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 &gt; s1.score
where s2.score IS NULL
group by s1.stage_level_id;
</code></pre>

The <code>left join</code> gives:

<pre><code>| 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) |
</code></pre>

The <code>where</code> clause selects the rows that contain the highest score(s) per <code>stage_level_id</code>:

<pre><code>| 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) |
</code></pre>

so that we can run <code>min(s1.moves)</code> on each <code>stage_level_id</code> group formulated by <code>group by</code> to get the final result:

<pre><code>| 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) |
</code></pre>

or 

<pre><code>| 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 |
</code></pre>

Test it out at <a href="http://sqlfiddle.com/#!9/50ed8/6/0" rel="nofollow noreferrer">http://sqlfiddle.com/#!9/50ed8/6/0</a>.

blocks|key|91620|text|我不确定我是否误解了问题，但它不是这么简单的吗：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|91621|SELECT+column1,+max(column2),+IF(column1+like+"%2553",+min(column3),+max(column3))
FROM+test
GROUP+BY+column1
;|code-block|syntax|javascript|91622|看看这个，SQLFiddle+link|offset|length|91623|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlfiddle.com/#!9/18b203/2^0|0|0|5|E|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

Not sure if I misunderstood the question but isnt it as simple as this:

<pre><code>SELECT column1, max(column2), IF(column1 like "%53", min(column3), max(column3))
FROM test
GROUP BY column1
;
</code></pre>

Check this out, <a href="http://sqlfiddle.com/#!9/18b203/2" rel="nofollow noreferrer">SQLFiddle link</a>

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

<pre><code>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
</code></pre>

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

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

<pre><code>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
</code></pre>

The expected result is:

<pre><code>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
</code></pre>

What I'm doing wrong?

Get MAX value from one column and MIN from another column

我一直在开发一个基于糖果粉碎传奇的游戏。Score表有以下三列：stage_level_id                        | value | moves------------------------------------------------------9f7678f0-fc8f-11e3-a3...

问从一列获取最大值，从另一列获取最小值
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从一列获取最大值，从另一列获取最小值EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从一列获取最大值，从另一列获取最小值
EN