【SQL】601. 体育馆的人流量（with as 临时表；id减去row_number()思路）

本文链接：https://blog.csdn.net/xiaoyue_/article/details/136616488

前述

知识点学习：

题目描述

leetcode题目：601. 体育馆的人流量

在这里插入图片描述

思路

关键：如何确定id是连续的三行或更多行记录

方法一：多次连表，筛选查询
方法二：借助 with as 临时表，id - row_number() over (order by id) as rk （俺这菜鸡没想到 id-row_number()，手动狗头）就能分成如下图：

【图片引自题解】

写法一：join

select distinct t1.*
from Stadium t1, Stadium t2, Stadium t3
where t1.people >= 100 and t2.people >= 100 and t3.people >= 100
and (
    (t1.id - t2.id = 1 and t1.id - t3.id = 2 and t2.id - t3.id = 1) or 
    (t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id = 1) or
    (t3.id - t2.id = 1 and t2.id - t1.id = 1 and t3.id - t1.id = 2)
)
order by t1.id;

写法二：with as 临时空间

在公司用hive常会用到这解法，有时候会在临时表里再多重嵌套

这道题需要提前用With临时空间，是因为where子句中需要再次调用from中选取的表
这里再聊一下sql的运行顺序：
from -> where -> group by -> select -> order by -> limit
即临时表t1 在from 和 where 中都会用到，因此需要提前定义。【引用题解，推荐阅读】

with t1 as (
    select *,
    id - row_number() over (order by id) as rk
    from Stadium
    where people >= 100
)

select id, visit_date, people
from t1 
where rk in (
    select rk 
    from t1
    group by rk
    having count(rk) >= 3
);