2020-11-11-Mysql(领扣-连续出现的数字)

image.png

1.如果序号连续的情况下解答

select
Log1.Num AS  ConsecutiveNums
from
Logs as Log1,Logs as Log2,Logs as Log3
where Log1.Num = Log2.Num  AND Log2.Num = Log3.Num and Log1.ID = Log2.ID -1 AND Log2.ID = Log3.ID-1

2.我的解答?未通过(与其他答案)

考虑ID是不连续的且是不重复的.采用 number() 窗口
如果ID字段与ID2字段相减得到的结果是一致的,我们可以确认它们是连续的
select
t3.Num
from
     (
select
distinct t2.Num,
COUNT(1)  AS result
from
(
select
t1.ID,
t1.Num,
(t1.ID - t1.ID2) as Result
from
(
select
ID,
Num,
row_number() over(partition by Num  order by ID) as ID2
from Logs) t1 ) t2
group by  t2.Num,t2.Result ) t3
where t3.result >=3;

select distinct Num ConsecutiveNums
from (select dif,num,count(*)
    from (select id,num,row_number() over(order by id)-row_number() over(partition by num order by id) dif from Logs) a
    group by dif,num having count(*)>=3) b;