获取mysql中的连续记录 [英] get consecutive records in mysql

问题描述

我的表格结构如下

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.

I wants to get consecutive wins(win=1) for each users in mysql.like for user_id=1, it should return 4(record id 10,11,12,13), for user_id=2(record id=5), it should return 1.

检索每个用户的记录后，我可以在php中做到这一点，但是我不知道如何使用对mysql的查询来做到这一点.

I can do that in php after retriving record for each user but I don't know how I can do it using query to mysql.

此外，使用php或mysql在性能方面会更好.任何帮助将不胜感激.谢谢！

Also what would be better in term of performance, using php or mysql. Any help will be appreciated.Thanks!!!

推荐答案

不确定您是否设法使另一个查询生效，但这是我的尝试，绝对有效-

Not sure if you've managed to get the other query to work but here's my attempt, definetly working - Sqlfiddle to prove it.

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页面上正常工作并测试是否能够获得正确的结果是我的基础，我还对查询进行了一些优化:

How to get it to work on a PHP page and testing to see you're getting the right results is underneath, I have also optimized the query a bit:

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);}

这篇关于获取mysql中的连续记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！