SQL-连续记录查询

记录一下一道秋招笔试SQL题目：【主要关于连续记录查询】

题目：X市建了一个新的体育馆，每日人流量信息记录在这三列的信息中，序号（id）、日期（visit_date)、人流量（people）。请编写一个查询语句，找到人流量的高峰期。高峰期时，至少连续三行记录中的人流量不少于100人。

例如：

对于上面的示例数据，输出结果为如下：

分析解答：【本文使用mysql】

1. 创建表格：

CREATE TABLE stadium 
(id INT auto_increment primary key not null,
visit_date date not null,
people INT not null);

2. 插入数据

INSERT INTO stadium VALUES
(1,"2019-01-01",10),
(2,"2019-01-02",109),
(3,"2019-01-03",150),
(4,"2019-01-04",99),
(5,"2019-01-05",146),
(6,"2019-01-06",1455),
(7,"2019-01-07",199),
(8,"2019-01-08",188);

3.解答思路：

（1）.  题目要求输出的时连续三行的记录，则可以选择三张单表进行自关联，连接的要求即为id序号的递增。连接方式为left join 

如下图即为无要求时的连接语句及结果：

SELECT s1.*,s2.*, s3.*
        FROM  stadium AS s1
        
        LEFT JOIN stadium AS s2
        ON s1.id+1 = s2.id
  
        LEFT JOIN stadium AS s3
        ON s1.id+2 = s3.id

有图可知，连接的每一行的 id 时从s1 到s2递增1， 再到s3递增1。 

再因为连接的每一行即为代表连续的三行的记录。根据题目要求，则要求people人数不小于100，即要求

这里连接的每一行的三个people 记录都得大于等于100。所以对上述连接结果进行一次where过滤

即加入。

（2） 过滤出连续得三行people不小于100得记录。

SELECT s1.*,s2.*, s3.*
        FROM  stadium AS s1
        
        LEFT JOIN stadium AS s2
        ON s1.id+1 = s2.id
  
        LEFT JOIN stadium AS s3
        ON s1.id+2 = s3.id

        WHERE s1.people>=100
        AND s2.people>=100 
        AND s3.people>=100

则输出结果为如下图：

此时输出得结果即为连接得即为满足条件的图。对比原连接图的输出即为

因为是左连接，所以s1最多只能到id=6. 后续s1的id=7.id=8不能匹配后续的s2、s3。但是输出结果需要s1的连续内容。即需要后续的id=7，id=8的内容。所以这里选择把上述求取的结果作为一个表。再连接一个原表。

（3）将上述求取结果作为一个表。再连接一个原表

注：这里不需要上述结果的所有内容。只需要连接后的所有id号即可。

取的上述（2）求解所有的id为  【与上述代码的区别只有在下代码中 注红的部分】

      (SELECT s1.id AS r1,s2.id AS r2, s3.id AS r3
        FROM  stadium AS s1
        
        LEFT JOIN stadium AS s2
        ON s1.id+1 = s2.id
  
        LEFT JOIN stadium AS s3
        ON s1.id+2 = s3.id
        
        WHERE s1.people>=100
        AND s2.people>=100 
        AND s3.people>=100
    )

结果为

观察，上述结果内所有出现的id都是会在连续表内出现。所以，取出所有id即可。与原表进行再连接。

连接条件即为原表的id和上述连接表的所有id相同。

代码如下：

SELECT s.* FROM stadium as s
 INNER JOIN 
     (SELECT s1.id AS r1,s2.id AS r2, s3.id AS r3
        FROM  stadium AS s1
        
        LEFT JOIN stadium AS s2
        ON s1.id+1 = s2.id
  
        LEFT JOIN stadium AS s3
        ON s1.id+2 = s3.id
        
        WHERE s1.people>=100
        AND s2.people>=100 
        AND s3.people>=100
    ) p
    WHERE (s.id = p.r1 or s.id = p.r2 or s.id = p.r3)

此时输出结果为

有输出结果图可知，id是有重复，而且顺序有些乱，此时需要对输出id进行唯一化处理（distinct）、再根据id排序。

最终的代码：

SELECT distinct s.* FROM stadium as s
 INNER JOIN 
     (SELECT s1.id AS r1,s2.id AS r2, s3.id AS r3
        FROM  stadium AS s1
        
        LEFT JOIN stadium AS s2
        ON s1.id+1 = s2.id
  
        LEFT JOIN stadium AS s3
        ON s1.id+2 = s3.id
        
        WHERE s1.people>=100
        AND s2.people>=100 
        AND s3.people>=100
    ) p
    WHERE (s.id = p.r1 or s.id = p.r2 or s.id = p.r3)
    order by s.id; 

最终输出结果为：