LeetCode（数据库）- 体育馆的人流量-阿里云开发者社区

题目大意：略。

解题思路：

解决方案(1)

第一步：查询人流量超过 100 的记录，然后将结果与其自身的临时表连接。

selectdistinctt1.*fromstadiumt1, stadiumt2, stadiumt3wheret1.people>=100andt2.people>=100andt3.people>=100;
|id|date|people|id|date|people|id|date|people||----|------------|--------|----|------------|--------|----|------------|--------||2|2017-01-02|109|2|2017-01-02|109|2|2017-01-02|109||3|2017-01-03|150|2|2017-01-02|109|2|2017-01-02|109||5|2017-01-05|145|2|2017-01-02|109|2|2017-01-02|109||6|2017-01-06|1455|2|2017-01-02|109|2|2017-01-02|109||7|2017-01-07|199|2|2017-01-02|109|2|2017-01-02|109||8|2017-01-08|188|2|2017-01-02|109|2|2017-01-02|109||2|2017-01-02|109|3|2017-01-03|150|2|2017-01-02|109||3|2017-01-03|150|3|2017-01-03|150|2|2017-01-02|109||5|2017-01-05|145|3|2017-01-03|150|2|2017-01-02|109||6|2017-01-06|1455|3|2017-01-03|150|2|2017-01-02|109||7|2017-01-07|199|3|2017-01-03|150|2|2017-01-02|109||8|2017-01-08|188|3|2017-01-03|150|2|2017-01-02|109||2|2017-01-02|109|5|2017-01-05|145|2|2017-01-02|109||3|2017-01-03|150|5|2017-01-05|145|2|2017-01-02|109||5|2017-01-05|145|5|2017-01-05|145|2|2017-01-02|109||6|2017-01-06|1455|5|2017-01-05|145|2|2017-01-02|109||7|2017-01-07|199|5|2017-01-05|145|2|2017-01-02|109||8|2017-01-08|188|5|2017-01-05|145|2|2017-01-02|109||2|2017-01-02|109|6|2017-01-06|1455|2|2017-01-02|109||3|2017-01-03|150|6|2017-01-06|1455|2|2017-01-02|109||5|2017-01-05|145|6|2017-01-06|1455|2|2017-01-02|109||6|2017-01-06|1455|6|2017-01-06|1455|2|2017-01-02|109||7|2017-01-07|199|6|2017-01-06|1455|2|2017-01-02|109||8|2017-01-08|188|6|2017-01-06|1455|2|2017-01-02|109||2|2017-01-02|109|7|2017-01-07|199|2|2017-01-02|109||3|2017-01-03|150|7|2017-01-07|199|2|2017-01-02|109||5|2017-01-05|145|7|2017-01-07|199|2|2017-01-02|109||6|2017-01-06|1455|7|2017-01-07|199|2|2017-01-02|109||7|2017-01-07|199|7|2017-01-07|199|2|2017-01-02|109||8|2017-01-08|188|7|2017-01-07|199|2|2017-01-02|109||2|2017-01-02|109|8|2017-01-08|188|2|2017-01-02|109||3|2017-01-03|150|8|2017-01-08|188|2|2017-01-02|109||5|2017-01-05|145|8|2017-01-08|188|2|2017-01-02|109||6|2017-01-06|1455|8|2017-01-08|188|2|2017-01-02|109||7|2017-01-07|199|8|2017-01-08|188|2|2017-01-02|109||8|2017-01-08|188|8|2017-01-08|188|2|2017-01-02|109||2|2017-01-02|109|2|2017-01-02|109|3|2017-01-03|150||3|2017-01-03|150|2|2017-01-02|109|3|2017-01-03|150||5|2017-01-05|145|2|2017-01-02|109|3|2017-01-03|150||6|2017-01-06|1455|2|2017-01-02|109|3|2017-01-03|150||7|2017-01-07|199|2|2017-01-02|109|3|2017-01-03|150||8|2017-01-08|188|2|2017-01-02|109|3|2017-01-03|150||2|2017-01-02|109|3|2017-01-03|150|3|2017-01-03|150||3|2017-01-03|150|3|2017-01-03|150|3|2017-01-03|150||5|2017-01-05|145|3|2017-01-03|150|3|2017-01-03|150||6|2017-01-06|1455|3|2017-01-03|150|3|2017-01-03|150||7|2017-01-07|199|3|2017-01-03|150|3|2017-01-03|150||8|2017-01-08|188|3|2017-01-03|150|3|2017-01-03|150||2|2017-01-02|109|5|2017-01-05|145|3|2017-01-03|150||3|2017-01-03|150|5|2017-01-05|145|3|2017-01-03|150||5|2017-01-05|145|5|2017-01-05|145|3|2017-01-03|150||6|2017-01-06|1455|5|2017-01-05|145|3|2017-01-03|150||7|2017-01-07|199|5|2017-01-05|145|3|2017-01-03|150||8|2017-01-08|188|5|2017-01-05|145|3|2017-01-03|150||2|2017-01-02|109|6|2017-01-06|1455|3|2017-01-03|150||3|2017-01-03|150|6|2017-01-06|1455|3|2017-01-03|150||5|2017-01-05|145|6|2017-01-06|1455|3|2017-01-03|150||6|2017-01-06|1455|6|2017-01-06|1455|3|2017-01-03|150||7|2017-01-07|199|6|2017-01-06|1455|3|2017-01-03|150||8|2017-01-08|188|6|2017-01-06|1455|3|2017-01-03|150||2|2017-01-02|109|7|2017-01-07|199|3|2017-01-03|150||3|2017-01-03|150|7|2017-01-07|199|3|2017-01-03|150||5|2017-01-05|145|7|2017-01-07|199|3|2017-01-03|150||6|2017-01-06|1455|7|2017-01-07|199|3|2017-01-03|150||7|2017-01-07|199|7|2017-01-07|199|3|2017-01-03|150||8|2017-01-08|188|7|2017-01-07|199|3|2017-01-03|150||2|2017-01-02|109|8|2017-01-08|188|3|2017-01-03|150||3|2017-01-03|150|8|2017-01-08|188|3|2017-01-03|150||5|2017-01-05|145|8|2017-01-08|188|3|2017-01-03|150||6|2017-01-06|1455|8|2017-01-08|188|3|2017-01-03|150||7|2017-01-07|199|8|2017-01-08|188|3|2017-01-03|150||8|2017-01-08|188|8|2017-01-08|188|3|2017-01-03|150||2|2017-01-02|109|2|2017-01-02|109|5|2017-01-05|145||3|2017-01-03|150|2|2017-01-02|109|5|2017-01-05|145||5|2017-01-05|145|2|2017-01-02|109|5|2017-01-05|145||6|2017-01-06|1455|2|2017-01-02|109|5|2017-01-05|145||7|2017-01-07|199|2|2017-01-02|109|5|2017-01-05|145||8|2017-01-08|188|2|2017-01-02|109|5|2017-01-05|145||2|2017-01-02|109|3|2017-01-03|150|5|2017-01-05|145||3|2017-01-03|150|3|2017-01-03|150|5|2017-01-05|145||5|2017-01-05|145|3|2017-01-03|150|5|2017-01-05|145||6|2017-01-06|1455|3|2017-01-03|150|5|2017-01-05|145||7|2017-01-07|199|3|2017-01-03|150|5|2017-01-05|145||8|2017-01-08|188|3|2017-01-03|150|5|2017-01-05|145||2|2017-01-02|109|5|2017-01-05|145|5|2017-01-05|145||3|2017-01-03|150|5|2017-01-05|145|5|2017-01-05|145||5|2017-01-05|145|5|2017-01-05|145|5|2017-01-05|145||6|2017-01-06|1455|5|2017-01-05|145|5|2017-01-05|145||7|2017-01-07|199|5|2017-01-05|145|5|2017-01-05|145||8|2017-01-08|188|5|2017-01-05|145|5|2017-01-05|145||2|2017-01-02|109|6|2017-01-06|1455|5|2017-01-05|145||3|2017-01-03|150|6|2017-01-06|1455|5|2017-01-05|145||5|2017-01-05|145|6|2017-01-06|1455|5|2017-01-05|145||6|2017-01-06|1455|6|2017-01-06|1455|5|2017-01-05|145||7|2017-01-07|199|6|2017-01-06|1455|5|2017-01-05|145||8|2017-01-08|188|6|2017-01-06|1455|5|2017-01-05|145||2|2017-01-02|109|7|2017-01-07|199|5|2017-01-05|145||3|2017-01-03|150|7|2017-01-07|199|5|2017-01-05|145||5|2017-01-05|145|7|2017-01-07|199|5|2017-01-05|145||6|2017-01-06|1455|7|2017-01-07|199|5|2017-01-05|145||7|2017-01-07|199|7|2017-01-07|199|5|2017-01-05|145||8|2017-01-08|188|7|2017-01-07|199|5|2017-01-05|145||2|2017-01-02|109|8|2017-01-08|188|5|2017-01-05|145||3|2017-01-03|150|8|2017-01-08|188|5|2017-01-05|145||5|2017-01-05|145|8|2017-01-08|188|5|2017-01-05|145||6|2017-01-06|1455|8|2017-01-08|188|5|2017-01-05|145||7|2017-01-07|199|8|2017-01-08|188|5|2017-01-05|145||8|2017-01-08|188|8|2017-01-08|188|5|2017-01-05|145||2|2017-01-02|109|2|2017-01-02|109|6|2017-01-06|1455||3|2017-01-03|150|2|2017-01-02|109|6|2017-01-06|1455||5|2017-01-05|145|2|2017-01-02|109|6|2017-01-06|1455||6|2017-01-06|1455|2|2017-01-02|109|6|2017-01-06|1455||7|2017-01-07|199|2|2017-01-02|109|6|2017-01-06|1455||8|2017-01-08|188|2|2017-01-02|109|6|2017-01-06|1455||2|2017-01-02|109|3|2017-01-03|150|6|2017-01-06|1455||3|2017-01-03|150|3|2017-01-03|150|6|2017-01-06|1455||5|2017-01-05|145|3|2017-01-03|150|6|2017-01-06|1455||6|2017-01-06|1455|3|2017-01-03|150|6|2017-01-06|1455||7|2017-01-07|199|3|2017-01-03|150|6|2017-01-06|1455||8|2017-01-08|188|3|2017-01-03|150|6|2017-01-06|1455||2|2017-01-02|109|5|2017-01-05|145|6|2017-01-06|1455||3|2017-01-03|150|5|2017-01-05|145|6|2017-01-06|1455||5|2017-01-05|145|5|2017-01-05|145|6|2017-01-06|1455||6|2017-01-06|1455|5|2017-01-05|145|6|2017-01-06|1455||7|2017-01-07|199|5|2017-01-05|145|6|2017-01-06|1455||8|2017-01-08|188|5|2017-01-05|145|6|2017-01-06|1455||2|2017-01-02|109|6|2017-01-06|1455|6|2017-01-06|1455||3|2017-01-03|150|6|2017-01-06|1455|6|2017-01-06|1455||5|2017-01-05|145|6|2017-01-06|1455|6|2017-01-06|1455||6|2017-01-06|1455|6|2017-01-06|1455|6|2017-01-06|1455||7|2017-01-07|199|6|2017-01-06|1455|6|2017-01-06|1455||8|2017-01-08|188|6|2017-01-06|1455|6|2017-01-06|1455||2|2017-01-02|109|7|2017-01-07|199|6|2017-01-06|1455||3|2017-01-03|150|7|2017-01-07|199|6|2017-01-06|1455||5|2017-01-05|145|7|2017-01-07|199|6|2017-01-06|1455||6|2017-01-06|1455|7|2017-01-07|199|6|2017-01-06|1455||7|2017-01-07|199|7|2017-01-07|199|6|2017-01-06|1455||8|2017-01-08|188|7|2017-01-07|199|6|2017-01-06|1455||2|2017-01-02|109|8|2017-01-08|188|6|2017-01-06|1455||3|2017-01-03|150|8|2017-01-08|188|6|2017-01-06|1455||5|2017-01-05|145|8|2017-01-08|188|6|2017-01-06|1455||6|2017-01-06|1455|8|2017-01-08|188|6|2017-01-06|1455||7|2017-01-07|199|8|2017-01-08|188|6|2017-01-06|1455||8|2017-01-08|188|8|2017-01-08|188|6|2017-01-06|1455||2|2017-01-02|109|2|2017-01-02|109|7|2017-01-07|199||3|2017-01-03|150|2|2017-01-02|109|7|2017-01-07|199||5|2017-01-05|145|2|2017-01-02|109|7|2017-01-07|199||6|2017-01-06|1455|2|2017-01-02|109|7|2017-01-07|199||7|2017-01-07|199|2|2017-01-02|109|7|2017-01-07|199||8|2017-01-08|188|2|2017-01-02|109|7|2017-01-07|199||2|2017-01-02|109|3|2017-01-03|150|7|2017-01-07|199||3|2017-01-03|150|3|2017-01-03|150|7|2017-01-07|199||5|2017-01-05|145|3|2017-01-03|150|7|2017-01-07|199||6|2017-01-06|1455|3|2017-01-03|150|7|2017-01-07|199||7|2017-01-07|199|3|2017-01-03|150|7|2017-01-07|199||8|2017-01-08|188|3|2017-01-03|150|7|2017-01-07|199||2|2017-01-02|109|5|2017-01-05|145|7|2017-01-07|199||3|2017-01-03|150|5|2017-01-05|145|7|2017-01-07|199||5|2017-01-05|145|5|2017-01-05|145|7|2017-01-07|199||6|2017-01-06|1455|5|2017-01-05|145|7|2017-01-07|199||7|2017-01-07|199|5|2017-01-05|145|7|2017-01-07|199||8|2017-01-08|188|5|2017-01-05|145|7|2017-01-07|199||2|2017-01-02|109|6|2017-01-06|1455|7|2017-01-07|199||3|2017-01-03|150|6|2017-01-06|1455|7|2017-01-07|199||5|2017-01-05|145|6|2017-01-06|1455|7|2017-01-07|199||6|2017-01-06|1455|6|2017-01-06|1455|7|2017-01-07|199||7|2017-01-07|199|6|2017-01-06|1455|7|2017-01-07|199||8|2017-01-08|188|6|2017-01-06|1455|7|2017-01-07|199||2|2017-01-02|109|7|2017-01-07|199|7|2017-01-07|199||3|2017-01-03|150|7|2017-01-07|199|7|2017-01-07|199||5|2017-01-05|145|7|2017-01-07|199|7|2017-01-07|199||6|2017-01-06|1455|7|2017-01-07|199|7|2017-01-07|199||7|2017-01-07|199|7|2017-01-07|199|7|2017-01-07|199||8|2017-01-08|188|7|2017-01-07|199|7|2017-01-07|199||2|2017-01-02|109|8|2017-01-08|188|7|2017-01-07|199||3|2017-01-03|150|8|2017-01-08|188|7|2017-01-07|199||5|2017-01-05|145|8|2017-01-08|188|7|2017-01-07|199||6|2017-01-06|1455|8|2017-01-08|188|7|2017-01-07|199||7|2017-01-07|199|8|2017-01-08|188|7|2017-01-07|199||8|2017-01-08|188|8|2017-01-08|188|7|2017-01-07|199||2|2017-01-02|109|2|2017-01-02|109|8|2017-01-08|188||3|2017-01-03|150|2|2017-01-02|109|8|2017-01-08|188||5|2017-01-05|145|2|2017-01-02|109|8|2017-01-08|188||6|2017-01-06|1455|2|2017-01-02|109|8|2017-01-08|188||7|2017-01-07|199|2|2017-01-02|109|8|2017-01-08|188||8|2017-01-08|188|2|2017-01-02|109|8|2017-01-08|188||2|2017-01-02|109|3|2017-01-03|150|8|2017-01-08|188||3|2017-01-03|150|3|2017-01-03|150|8|2017-01-08|188||5|2017-01-05|145|3|2017-01-03|150|8|2017-01-08|188||6|2017-01-06|1455|3|2017-01-03|150|8|2017-01-08|188||7|2017-01-07|199|3|2017-01-03|150|8|2017-01-08|188||8|2017-01-08|188|3|2017-01-03|150|8|2017-01-08|188||2|2017-01-02|109|5|2017-01-05|145|8|2017-01-08|188||3|2017-01-03|150|5|2017-01-05|145|8|2017-01-08|188||5|2017-01-05|145|5|2017-01-05|145|8|2017-01-08|188||6|2017-01-06|1455|5|2017-01-05|145|8|2017-01-08|188||7|2017-01-07|199|5|2017-01-05|145|8|2017-01-08|188||8|2017-01-08|188|5|2017-01-05|145|8|2017-01-08|188||2|2017-01-02|109|6|2017-01-06|1455|8|2017-01-08|188||3|2017-01-03|150|6|2017-01-06|1455|8|2017-01-08|188||5|2017-01-05|145|6|2017-01-06|1455|8|2017-01-08|188||6|2017-01-06|1455|6|2017-01-06|1455|8|2017-01-08|188||7|2017-01-07|199|6|2017-01-06|1455|8|2017-01-08|188||8|2017-01-08|188|6|2017-01-06|1455|8|2017-01-08|188||2|2017-01-02|109|7|2017-01-07|199|8|2017-01-08|188||3|2017-01-03|150|7|2017-01-07|199|8|2017-01-08|188||5|2017-01-05|145|7|2017-01-07|199|8|2017-01-08|188||6|2017-01-06|1455|7|2017-01-07|199|8|2017-01-08|188||7|2017-01-07|199|7|2017-01-07|199|8|2017-01-08|188||8|2017-01-08|188|7|2017-01-07|199|8|2017-01-08|188||2|2017-01-02|109|8|2017-01-08|188|8|2017-01-08|188||3|2017-01-03|150|8|2017-01-08|188|8|2017-01-08|188||5|2017-01-05|145|8|2017-01-08|188|8|2017-01-08|188||6|2017-01-06|1455|8|2017-01-08|188|8|2017-01-08|188||7|2017-01-07|199|8|2017-01-08|188|8|2017-01-08|188||8|2017-01-08|188|8|2017-01-08|188|8|2017-01-08|188|

共有 6 天人流量超过 100 人，笛卡尔积后有 216（666）条记录。
前 3 列来自表 t1，中间 3 列来自表 t2，最后 3 列来自表 t3。

表 t1，t2 和 t3 相同，需要考虑添加哪些条件能够得到想要的结果。以 t1 为例，它有可能是高峰期的第 1 天，第 2 天，或第 3 天。

t1 是高峰期第 1 天：(t1.id - t2.id = 1 and t1.id - t3.id = 2 and t2.id - t3.id =1) -- t1, t2, t3
t1 是高峰期第 2 天：(t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id =1) -- t2, t1, t3
t1 是高峰期第 3 天：(t3.id - t2.id = 1 and t2.id - t1.id =1 and t3.id - t1.id = 2) -- t3, t2, t1

selectt1.*fromstadiumt1, stadiumt2, stadiumt3wheret1.people>=100andt2.people>=100andt3.people>=100and(
    (t1.id-t2.id=1andt1.id-t3.id=2andt2.id-t3.id=1) --t1, t2, t3or    (t2.id-t1.id=1andt2.id-t3.id=2andt1.id-t3.id=1) --t2, t1, t3or    (t3.id-t2.id=1andt2.id-t1.id=1andt3.id-t1.id=2) --t3, t2, t1);
|id|date|people||----|------------|--------||7|2017-01-07|199||6|2017-01-06|1455||8|2017-01-08|188||7|2017-01-07|199||5|2017-01-05|145||6|2017-01-06|1455|

可以看到查询结果中存在重复的记录，再使用 DISTINCT 去重。

selectdistinctt1.*fromstadiumt1, stadiumt2, stadiumt3wheret1.people>=100andt2.people>=100andt3.people>=100and(
    (t1.id-t2.id=1andt1.id-t3.id=2andt2.id-t3.id=1) --t1, t2, t3or    (t2.id-t1.id=1andt2.id-t3.id=2andt1.id-t3.id=1) --t2, t1, t3or    (t3.id-t2.id=1andt2.id-t1.id=1andt3.id-t1.id=2) --t3, t2, t1)
orderbyt1.id;

解答疑惑

解惑1：高峰顺序貌似搞反了吧，应该是以下写法才说得通。不过不影响执行的结果。而且不需要 ORDER BY，但也不能说上面写错了，因为人家倒过来想，只要最后 ORDER BY 下即可~

t1是高峰期第1天：(t1.id-t2.id=-1andt1.id-t3.id=-2andt2.id-t3.id=-1) --t1, t2, t3t1是高峰期第2天：(t2.id-t1.id=-1andt2.id-t3.id=-2andt1.id-t3.id=-1) --t2, t1, t3t1是高峰期第3天：(t3.id-t2.id=-1andt2.id-t1.id=-1andt3.id-t1.id=-2) --t3, t2, t1

解惑2：为啥是这 3 种情况，而不是 6 种情况排列组合呢？比如：有 t1，t2，t3，为啥没有 t1，t3，t2 的情况呢？

其实本质是一样的 t1，t2，t3 和 t1，t3，t2 结果肯定是一样的，多此一举罢了。但是上面这 3 种分别都是不一样的，本质不一样，自己感觉下！

解惑3：题目不是说大于等于 3 天，那假如是连续 4 天呢，第 4 天的判断为何没有？

其实仔细想下上面的公式，一个连续 4 天，能不能看成 2 个连续的 3 天组成，懂了吧！

解决方案(2) （MySQL 8.0+）

使用 row_number() over() 函数 + with as 临时表语法

witht1as(
select*,id-row_number() over(orderbyid) asrkfromstadiumwherepeople>=100)
selectid,visit_date,peoplefromt1whererkin(
selectrkfromt1groupbyrkhavingcount(rk) >=3)

通过 row_number() over() 排名 1...N，从 id - 排名得最终连续的规律，牛皮~

解决方案(3) （MySQL 8.0+）

使用自定义变量做标记 + with as 临时表语法

WITHt2AS(
WITHt1AS(
SELECTid, visit_date, peopleFROMStadiumWHEREpeople>=100    )
SELECTid, visit_date, people, @gid:=IF(id-@preId=1, @gid, @gid+1) groupId, @preId:=idFROMt1, (SELECT@preId:=(SELECTidFROMt1LIMIT0, 1), @gid:=0) init)
SELECTid, visit_date, peopleFROMt2WHEREgroupIdIN (
SELECTgroupIdFROMt2GROUPBYgroupIdHAVINGCOUNT(groupId) >=3)

下面查询思路和（2）都一样，关键在于上面 with as 部分：这边思路是从第一条开始做标记数字1，如果中间断了不连续的话，就 +1，连续则继续复用上次的标记数值，最终效果是和（2）一样的如上图所示。

注意：这里的临时表有 2 个，不能并级写，只能嵌套的写法。

// 反例: 运行错误WITHt1AS(
SELECTid, visit_date, peopleFROMStadiumWHEREpeople>=100)
WITHt2AS(
SELECTid, visit_date, people, @gid:=IF(id-@preId=1, @gid, @gid+1) groupId, @preId:=idFROMt1, (SELECT@preId:=(SELECTidFROMt1LIMIT0, 1), @gid:=0) init)