在MySQL中获取连续记录

Question

我有以下表结构：

ID           user_id         win 
1              1               1 
2              1               1 
3              1               0 
4              1               1 
5              2               1 
6              2               0 
7              2               0 
8              2               1 
9              1               0 
10             1               1 
11             1               1 
12             1               1 
13             1               1
14             3               1 

我希望在MySQL中为每个用户获取连续的胜利记录（win=1）。例如，对于user_id=1，应返回4条记录（id为10、11、12、13），对于user_id=2（id为5），应返回1条记录。

我可以在php中检索每个用户的记录后执行此操作，但我不知道如何使用MySQL查询来实现。

此外，在性能方面，使用php还是MySQL更好？任何帮助都将不胜感激。谢谢！

- Ram

---

不，因为1、2、4、9不是连续的数字。用户ID为1在记录ID为3中失败了。 - Ram

你赢了1,2场比赛，你想要最长的连胜纪录吗？ - user557846

是的，确实。抱歉，在问题中我没有提到它。我想要最长的连胜记录。谢谢。 - Ram

Answer 1

内部查询计算每个连续的数量。外部查询获取每个用户的最大值。查询未经测试（但基于有效的查询）

set @user_id = null;
set @streak = 1;

select user_id, max(streak) from (
  SELECT user_id, streak,
    case when @user_id is null OR @user_id != user_id then @streak := 1 else @streak := @streak + 1 end as streak_formula,
    @user_id := user_id,
    @streak as streak
  FROM my_table
) foo
group by user_id

- Alain Collins

---

我正在测试它。很快会让你知道。 - Ram

我无法在mysql上运行它。您能否详细解释一下，以便我可以纠正我的错误。我已经将“my_table”替换为我的表名。还有其他需要更改的地方吗？ - Ram

你遇到了什么错误？my_table表中是否包含名为user_id的列？ - Alain Collins

Answer 2

不确定您是否已经成功地处理了其他查询，但这是我的尝试，绝对有效-Sqlfiddle可以证明。

set @x=null;
set @y=0;

select sub.user_id as user_id,max(sub.streak) as streak
from
(
select 
case when @x is null then @x:=user_id end,
case 
when win=1 and @x=user_id then @y:=@y+1 
when win=0 and @x=user_id then @y:=0 
when win=1 and @x<>user_id then @y:=1
when win=0 and @x<>user_id then @y:=0
end as streak,
@x:=user_id as user_id
from your_table
) as sub
group by sub.user_id

如何在 PHP 页面上运行它并测试以确保您获取了正确的结果，下面是优化过查询的方法：

mysql_query("set @y=0");
$query=mysql_query("select sub.user_id as user_id,max(sub.streak) as streak
from
(
select
case 
when win=1 and @x=user_id then @y:=@y+1 
when win=0 and @x=user_id then @y:=0 
when win=1 and @x<>user_id then @y:=1
when win=0 and @x<>user_id then @y:=0
end as streak,
@x:=user_id as user_id
from your_table
) as sub
group by sub.user_id");
while($row=mysql_fetch_assoc($query)){
print_r($row);}

- mrmryb

---

嗨，使用PHP中的mysql_query函数执行此查询未返回任何内容，而在phpmyadmin上运行正常。对此有什么建议。谢谢。 - Ram

@PriteshGupta 你好，我已经编辑了我的帖子，包括我如何在我制作的php页面上使其工作。 - mrmryb

@PriteshGupta 在其他人说之前，我要提醒你：mysql_query现在已经过时了，你应该使用mysqli或PDO，但我已经用mysql_query编写了代码，因为我不希望你突然改变一切。但是学习它是值得的-个人建议使用PDO。 - mrmryb

非常感谢您在这里的帮助，并指出我应该使用PDO。谢谢。 - Ram

这个查询存在一个问题，如果用户的记录不是连续的，那么它将给出错误的结果。例如，如果userid=1有2次连续获胜，然后是user_id=2的下2条记录，然后如果user_id再次获胜，它将从1开始计数，而应该从3开始计数。因此，我认为我需要为每个用户迭代以获取该用户的连续获胜。对此还有什么建议吗？ - Ram