《学习MySQL语句》——第五天

 

#注意连续出现三次，连续两个字
#还要去重
select distinct a.Num as ConsecutiveNums
from Logs as a, Logs as b, Logs as c
where a.Num = b.Num and b.Num = c.Num and a.id = b.id - 1 and b.id = c.id - 1;



利用窗口函数求出diff后进行分组，再用having子句过滤
#row_number() over(partition by 分组列 order by 排序列 desc)
#having子句本身是针对分组统计结果进行条件筛选的
select distinct num as ConsecutiveNums
from (select num, id + 1 - row_number() over(partition by num order by id) as diff
      from Logs) t
group by num, diff
having count(*) >= 3