找出连续出现N次的内容
题目:下面是学生的成绩表(表名score,列名:学号、成绩),使用SQL查找所有至少连续出现3次的成绩)
给定上面的 Logs 表, 1 是唯一连续出现至少三次的数字。
考察点:自联结表的使用
思路分析:连续出现三次,也就是三个连续的学号,他们的成绩相等。由题目可知,学号是按顺序存储的,也就是说:某一学号=下一学号-1;下一学号=下下学号-1;如果学号不是连续的,我们可以增加一列连续的值代替。自联结表,也就是自身与自身关联,我们要求连续三次以上,那么我可以利用自联结表将表复制三份 A, B, C。
连续三次也就是 A.学号 =B.学号-1,and B.学号 =C.学号-1
SELECT DISTINCT
A.Num as ConsecutiveNums
from Logs A,
Logs B,
Logs C
where A.Id = B.Id -1
and B.Id = C.Id-1
and A.Num = B.Num
and B.Num = C.Num;
使用用户变量,按个遍历Num列,第一个case when 每次把Num的值赋值给变量@prev(局部变量),同时@count计数加1,第二个when,当Num不等于之前的值时,令@count为1 重现开始计数。
select distinct Num as ConsecutiveNums
from (
select Num,
case
when @prev = Num then @count := @count + 1
when (@prev := Num) is not null then @count := 1
end as CNT
from Logs, (select @prev := null,@count := null) as t
) as temp
where temp.CNT >= 3
select Num,
case
when @prev = Num then @count := @count + 1
when (@prev := Num) is not null then @count := 1
end as CNT
from Logs, (select @prev := null,@count := null) as t
{“headers”: [“Num”, “CNT”], “values”: [[1, 1.0], [1, 2.0], [1, 3.0], [2, 1.0], [1, 1.0], [2, 1.0], [2, 2.0]]},这段结果就是上面代码执行的结果。