MySQL:计算一个值连续出现的次数

本文介绍了MySQL:计算一个值连续出现的次数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我做了一些谷歌搜索来尝试找到这个答案，甚至遇到了 SQL Server 的 OVER() 函数和一篇关于如何模拟的文章......但它高于我的工资等级:P

I've done a bit of Googling to try and find this answer and even came across the OVER() function for SQL Server and an article on how to emulate...but it's above my pay grade :P

我会保持简单，假设我有一个学生表和一个操作列.我想创建一个查询来计算他们举手、不举手或不上课的连续次数.没有周末课，所以它必须计算实际记录，而不仅仅是今天-FirstFoundDate.如果学生 A 连续举手 3 周(即 15=5 天 X 3 周)但没有出现在课堂上，则计数为 0.

I'll keep it simple and say I have a table of students and an action column. I want to create a query that will count the number of consecutive times that they either raised their hand, didn't raise their hand, or didn't attend class. There are no weekend classes, so it has to count the actual records, not just today-FirstFoundDate. If student A raises their hand for 3 weeks straight (that count would be 15=5days X 3weeks) but then doesn't show up to class, the count will be 0.

有什么想法吗?

谢谢，

推荐答案

我一直在考虑这个，让我们重新开始...

I've been thinking about this, and let's start over...

怎么样

select * from student_action maindata where not exist (select * from student_action action_count where maindata.student_id = action_count.student_id and maindata.action_timestamp < action_count.action_timestamp and maindata.action <> action_count.action)

这应该给我们表中的所有连续动作，每次连续出现一行.然后 group by 将计算它们.

This should give us all the consecutive actions in the table, one row for each consecutive occurrence. Then the group by will count them.

select count(*), maindata.student_id, maindata.action from student_action maindata where not exist (select * from student_action action_count where maindata.student_id = action_count.student_id and maindata.action_timestamp < action_count.action_timestamp and maindata.action <> action_count.action) group by maindata.student_id, maindata.action