力扣180题_连续出现的数字

1.题目:

 

2.解题思路:

3.解题:

# 1. 按照num进行分组
select *,row_number() OVER (PARTITION BY num order by id) as dcsl FROM Logs;

WITH t1 AS (
    select *,row_number() OVER (PARTITION BY num order by id) as dcsl FROM Logs
)
select *,id - dcsl as chazhi from t1;

# 2.计数
WITH
    t1 AS (
    select *,row_number() OVER (PARTITION BY num order by id) as dcsl FROM Logs
),
    t2 AS (
        select *,id - dcsl as chazhi from t1
    )
select id,num,dcsl,count(chazhi) over(PARTITION BY num,chazhi) jishu from t2;

# 3. 筛选,大于等于三的
WITH
    t1 AS (
    select *,row_number() OVER (PARTITION BY num order by id) as dcsl FROM Logs
),
    t2 AS (
        select *,cast(id as unsigned) - cast(dcsl as unsigned) as chazhi from t1
    ),
    t3 as(
    select id,num,dcsl,count(chazhi) over(PARTITION BY num,chazhi) jishu from t2
    )
select num as ConsecutiveNums from t3 where  jishu >=3 GROUP BY num;

注意:

这里id - dcsl 要改为cast(id as unsigned) - cast(dcsl as unsigned)  (我也不知道为什么,别人帮我解决的),不然会报错

答案:

WITH
    t1 AS (
    select *,row_number() OVER (PARTITION BY num order by id) as dcsl FROM Logs
),
    t2 AS (
        select *,cast(id as signed) - cast(dcsl as signed) as chazhi from t1
    ),
    t3 as(
    select id,num,dcsl,count(chazhi) over(PARTITION BY num,chazhi) jishu from t2
    )
select num as ConsecutiveNums from t3 where  jishu >=3 GROUP BY num;