mysql - MySQL-对行进行计数，直到达到第一个不同的值

我试图计算每一个不同值的行数，直到第一次出现不同值为止。
表格示例

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

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

最佳答案

您可以在相关子查询中统计最后未赢的游戏。

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