前述
知识点学习:
- with as 和临时表的使用
- 12、关于临时表和with as子查询部分
题目描述
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 rkfrom Stadiumwhere people >= 100
)select id, visit_date, people
from t1
where rk in (select rk from t1group by rkhaving count(rk) >= 3
);
部分过程解析: