甘州区建设局网站wordpress四川华体

前述

知识点学习：

1. with as 和临时表的使用
2. 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
);

部分过程解析：