mysql排名函数row_number()over(order by)和with * as 的用法

601. 体育馆的人流量(力扣mysql题,难度:困难)

表：Stadium

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| visit_date    | date    |
| people        | int     |
+---------------+---------+

visit_date 是该表中具有唯一值的列。
每日人流量信息被记录在这三列信息中：序号 (id)、日期 (visit_date)、 人流量 (people)
每天只有一行记录，日期随着 id 的增加而增加

要求:编写解决方案找出每行的人数大于或等于 100 且 id 连续的三行或更多行记录。

返回按 visit_date 升序排列 的结果表。

查询结果格式如下所示。

示例:

输入：
Stadium 表:

+------+------------+-----------+
| id   | visit_date | people    |
+------+------------+-----------+
| 1    | 2017-01-01 | 10        |
| 2    | 2017-01-02 | 109       |
| 3    | 2017-01-03 | 150       |
| 4    | 2017-01-04 | 99        |
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-09 | 188       |
+------+------------+-----------+

输出：

+------+------------+-----------+
| id   | visit_date | people    |
+------+------------+-----------+
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-09 | 188       |
+------+------------+-----------+

解释：
id 为 5、6、7、8 的四行 id 连续，并且每行都有 >= 100 的人数记录。
请注意，即使第 7 行和第 8 行的 visit_date 不是连续的，输出也应当包含第 8 行，因为我们只需要考虑 id 连续的记录。
不输出 id 为 2 和 3 的行，因为至少需要三条 id 连续的记录。

解题代码

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 rkfrom t1group by rkhaving count(rk) >= 3
)

代码分析:
1.select *,id - row_number() over(order by id) as rk 和 select *,row_number() over(order by id) as rk的区别

select *,id - row_number() over(order by id) as rk from stadium where people >= 100;
//输出结果
/*
| id | visit_date | people | rk |
| -- | ---------- | ------ | -- |
| 2  | 2017-01-02 | 109    | 1  |
| 3  | 2017-01-03 | 150    | 1  |
| 5  | 2017-01-05 | 145    | 2  |
| 6  | 2017-01-06 | 1455   | 2  |
| 7  | 2017-01-07 | 199    | 2  |
| 8  | 2017-01-09 | 188    | 2  |
*/
select *,row_number() over(order by id) as rk from stadium where people >= 100;
//输出结果
/*
| id | visit_date | people | rk |
| -- | ---------- | ------ | -- |
| 2  | 2017-01-02 | 109    | 1  |
| 3  | 2017-01-03 | 150    | 2  |
| 5  | 2017-01-05 | 145    | 3  |
| 6  | 2017-01-06 | 1455   | 4  |
| 7  | 2017-01-07 | 199    | 5  |
| 8  | 2017-01-09 | 188    | 6  |
*/

2.with t1 as的用法

定义:WITH 子句是 MySQL 中的一种 SQL 结构，又称为 Common Table Expression (CTE)。它在不影响原有 SQL 语句的情况下，允许开发人员临时创建一个内存中的结果集，然后对其进行操作。

语法:WITH cte_name (column_name1, column_name2, …) AS ( SELECT column1, column2, … FROM table WHERE condition )

说明:其中，cte_name 是 WITH 子句的名称；column_name1、column_name2 等是结果集的列名；SELECT 子句定义了该结果集的内容；condition 是查询的过滤条件。
结合上述代码:

with t1 as(select *,id - row_number() over(order by id) as rkfrom stadiumwhere people >= 100
)
//就是将stadium表里面的数据查询出来放在一个名为t1的临时表(不存在的)中
/*
| id | visit_date | people | rk |
| -- | ---------- | ------ | -- |
| 2  | 2017-01-02 | 109    | 1  |
| 3  | 2017-01-03 | 150    | 1  |
| 5  | 2017-01-05 | 145    | 2  |
| 6  | 2017-01-06 | 1455   | 2  |
| 7  | 2017-01-07 | 199    | 2  |
| 8  | 2017-01-09 | 188    | 2  |
*/
*/

select id,visit_date,people
from t1
where rk in(select rkfrom t1group by rkhaving count(rk) >= 3
)
//然后再将t1临时表中的数据进行group by *** having 分组聚合查询,最后得出数据
/*
| id | visit_date | people |
| -- | ---------- | ------ |
| 5  | 2017-01-05 | 145    |
| 6  | 2017-01-06 | 1455   |
| 7  | 2017-01-07 | 199    |
| 8  | 2017-01-09 | 188    |
*/

注意:with t1 as 在mysql8.0后才可用,with t1 as (select * from stadium ) 后面要和 select 联用,不能单独使用with t1 as