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;
网友评论