获取连续行mysql

Question

我正在创建一个Rock Papper Scisors游戏，我面临的问题是，我需要根据3、5或7的最佳值来设置一个赢家，为此我需要计算连续查询的次数--我的投注表很简单：

BO3

ID|GAME_ID|WINNER
1 |145    |15
2 |145    |14
3 |145    |15
4 |145    |15

15需要赢，我怎么能在mysql中计算呢？

例：

GAME_ID|WINNER|CONSECUTIVES
    145|15    |2

非常感谢。

Answer 1

我认为您需要这方面的变量：

select gameid, winner, max(rn)
from (select s.*,
             (@rn := if(@gw = concat_ws(':', gameid, winner), @rn + 1,
                        if(@gw := concat_ws(':', gameid, winner), 1, 1)
                       )
             ) as rn
      from scores s cross join
           (select @gw := '', @rn := 0) params
      order by s.id
     ) s
group by gameid, winner;

这里是一个SQL。

Answer 2

也许是这样的：

select y.winner, 
  case when y2.cnt <= 3 then 'Best of 3' 
       when y2.cnt <= 5 then 'Best of 5' 
       when y2.cnt <= 7 then 'Best of 7' 
   end, count(*)
from yourtable y join (
  select count(*) cnt, gameid
  from yourtable
  group by gameid) y2 on y.gameid = y2.gameid
group by y.gameid, y.winner, y2.cnt
having count(*) = 2 and y2.cnt <= 3 or 
 count(*) = 3 and y2.cnt <= 5 or 
 count(*) = 4 and y2.cnt <= 7

• SQL Fiddle演示