试题来自力扣

https://leetcode-cn.com/problems/consecutive-numbers/

1. 构造数据

create table logs (id int primary key ,num int);
insert into logs values(1,1);
insert into logs values(2,1);
insert into logs values(3,1);
insert into logs values(4,2);
insert into logs values(5,1);
insert into logs values(6,2);
insert into logs values(7,2);
insert into logs values(8,3);
insert into logs values(9,2);
insert into logs values(10,3);
insert into logs values(11,3);
insert into logs values(12,3);
insert into logs values(13,13);
insert into logs values(14,13);
insert into logs values(15,13);

2.解题

方法1 三表并联

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;

执行结果

 

 方法2 使用变量

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;

使用 函数2

select distinct Num as ConsecutiveNums 
from (
    select Num,
           @cnt:=if(@pre=Num,@cnt:=@cnt+1,@cnt:=1) cnt,
           @pre:=Num pre
    from logs,(select @cnt:=0,@pre:=null) a)b
where b.cnt>=3;

 

  方法 3 使用row_number() 窗口函数

select distinct o.Num as ConsecutiveNums
from (select id,num, row_number() over(partition by num) as nb from logs) o
group by o.Num,o.id + 1 - o.nb
having count(1) >=3;

 

稍改一下

select distinct(num) "ConsecutiveNums"
from (
    select num,(row_number() over(order by id )-row_number() over(partition by num order by id)) rank_ from logs) tmp
group by rank_,num
having count(rank_)>=3;

 

   方法 4 使用lead() 窗口函数

select distinct Num as ConsecutiveNums 
from (
      select id,Num, lead(Num,1) over(order by id) as Num2,lead(Num,2) over(order by id) as Num3 from logs ) a
where Num=Num2 and Num2=Num3;

 

    方法 5 使用lag() 窗口函数

select distinct Num as ConsecutiveNums 
from (
      select id,Num, lag(Num,1) over(order by id) as Num2,lag(Num,2) over(order by id) as Num3 from logs ) a
where Num=Num2 and Num2=Num3;

 

 方法6 使用lead() 和lag() 窗口函数  （最快的SQL）

select distinct num as ConsecutiveNums from 
(
    select id,num,lag(num) over(order by id) as lags,lead(num) over(order by id) as leads from logs) as v 
where num = lags and num = leads

 

 总结