前言

for continuous problems,在不采用SQL变量的情况下,It requires clever grouping（问题转换）,Sort by a specific field,当序号id - 排序值rank 相等时,Indicates that two specific records are consecutive.

一、连续出现的数字

二、三种解法

1、violent couplet

select distinct l1.Num ConsecutiveNums
from Logs l1 join Logs l2 on l1.id + 1 = l2.id and l1.num = l2.num
    join Logs l3 on l2.id + 1 = l3.id and l2.num = l3.num

2、Cleverly grouped

with view4row as(
    select Id,num,
        # 防止id不连续.
        row_number() over() `row`
    from Logs
),view4rank as(
    select id,num,`row`,
    # 注：rank()Waiting for sorting will mess up thatview的记录顺序.
    rank() over(partition by num order by `row`) rk
    from view4row
)
select distinct Num ConsecutiveNums 
from view4rank
#`row` - rkEqual means innumon an equal basis,These are equalnum是连续的,This is in analysis How to group questions 的转换.
group by Num,`row` - rk 
having count(1) > 2

3、加入变量

with view4base as(
    select num,
        case
            when num = @pre then @cnt:=@cnt+1
            when true       then @cnt:=1
        end cnt,
        @pre:=num
    from (select num from Logs) t1,(select @cnt:=1,@pre:='') t2 # Cartesian product hits the variable to the end of each record.
)
select distinct Num ConsecutiveNums 
from view4base
where cnt > 2

总结

1）连续问题,It is necessary to target the specific fields of the requirements,Sort by their groupings,通过序号id - 排序值rankto group consecutive ones into groups.
2）In addition to the clever solution of problem transformation,It is also possible to assign variable fields to each row of records through a Cartesian product,通过操作变量,让SQL更灵活,以达到SQLprogramming effect.

参考文献

[1] LeetCode 连续出现的数字