我有如下的表结构
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在性能方面会更好。任何帮助都将不胜感激。谢谢
内部查询统计每条条纹。外部查询获取每个用户的最大值。查询未经测试(但基于有效的查询)
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
不确定你是否已经设法让另一个查询工作,但这是我的尝试,明确地工作-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);}