Mysql - 计算行直到达到第一个不同的值

Question

我正在尝试计算每个不同值第一次出现不同值之前的行数。

表格示例

game winner
-----------
1    Mark
2    Joe
3    Mark
4    Paula
5    Paula
6    Paula
7    Joe
8    Anna

通过下面的查询我得到了这个..

SELECT winner,COUNT(*) as count FROM tablename GROUP BY winner;

结果

Mark won 2 games
Joe won 2 games
Paula won 3 games
Anna won 1 game

下面是我想要得到的结果:

Mark won 2 games, but didn't won last 6 games 
Joe won 2 games, but didn't won last 1 games 
Paula won 3 games, but didn't won last 2 games 
Anna won 1 game, but didn't won last 0 games

谢谢你抽出时间来帮助我，我真的很感激。

Answer 1

您可以在相关子查询中计算最后一场未获胜的比赛。

select winner, count(*) as won, (
  select count(*)
  from tablename t2
  where t2.game > max(t1.game)
) as not_won
from tablename t1
group by winner

演示:https://www.db-fiddle.com/f/czHPqscvEGgLPLeVYHV5hk/0