id || week_id || user_id || catch_id(0,1,2)
1 || 2 || 6 || 0
1 || 3 || 6 || 1个
1 || 4 || 6 || 1个
1 || 5 || 6 || 1个
1 || 6 || 6 || 0
1 || 7 || 6 || 0
1 || 8 || 6 || 2个
1 || 9 || 6 || 0
1 || 10 || 6 || 0
1 || 11 || 6 || 1个
我需要找出每个用户的最大连续周 catch = 1 和最大连续周 catch = 0 (查找全部)。我希望我能说清楚。
在上表中
用户6的最大连续捕获= 1为 3 (第3、4、5周)
用户6的最大连续周捕获量= 0为 2 (第6,7周和/或第9,10周)
我该怎么办。我可以在纯sql中执行此操作吗？也欢迎使用php解决方案
最佳答案
这应该适用于SQL解决方案。尽管它只会为您提供有关问题catch_id的一个week_id。我不知道您的表格叫什么，所以我在以下答案中将其称为consecutive:
drop table if exists consecutive;
create table consecutive
(id int,week_id int,user_id int,catch_id int);
insert into consecutive values (1,2,6,0);
insert into consecutive values (1,3,6,1);
insert into consecutive values (1,4,6,1);
insert into consecutive values (1,5,6,1);
insert into consecutive values (1,6,6,0);
insert into consecutive values (1,7,6,0);
insert into consecutive values (1,8,6,2);
insert into consecutive values (1,9,6,0);
insert into consecutive values (1,10,6,0);
insert into consecutive values (1,11,6,1);
select w,count(*) as max_consecutive_weeks
from
(
select
case when @cur_catch_id != catch_id then @cur_week_id := week_id else @cur_week_id end as w,
@cur_catch_id := catch_id as catchChange,
c.*
from consecutive c
cross join (select @cur_catch_id := -1,@cur_week_id := -1) t
where user_id = 6
order by week_id asc
) t
where catch_id = 1
group by w
order by max_consecutive_weeks desc,w asc
limit 1;
您可以使用相同的查询，通过将
where catch_id = 1更改为where catch_id = 0来获取具有catch_id = 0的最大连续week_ids。祝你好运!
关于php - 查找最大连续记录数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/8006521/