Leetcode SQL 刷题 601题

---

题目描述

体育馆的人流量

编写一个 SQL 查询以找出每行的人数大于或等于 100 且 id 连续的三行或更多行记录。
返回按 visit_date 升序排列的结果表。

---

一、分析思路

1.自连接
找到连续三条记录以上的id，并且满足三条记录均大于等于100
首先三个表自连接，第一条查询语句找出三条记录的第一个ID
第二条查询语句找出三条记录的第二个ID
第三条查询语句找出三条记录的第三个ID

   (SELECT s1.Id
    FROM Stadium s1,Stadium s2,Stadium s3
    WHERE (s1.Id=s2.Id-1 AND s2.Id=s3.Id-1) AND
    (s1.people>=100 and s2.people>=100 and s3.people>=100) )
    UNION 
    (SELECT s2.Id
    FROM Stadium s1,Stadium s2,Stadium s3
    WHERE (s1.Id=s2.Id-1 AND s2.Id=s3.Id-1) AND
    (s1.people>=100 and s2.people>=100 and s3.people>=100) )
    UNION
    (SELECT s3.Id
    FROM Stadium s1,Stadium s2,Stadium s3
     WHERE (s1.Id=s2.Id-1 AND s2.Id=s3.Id-1) AND
    (s1.people>=100 AND s2.people>=100 AND s3.people>=100))  
     

根据找到的Id对Stadium进行筛选和排序

SELECT *
FROM Stadium 
WHERE Id IN (
    (SELECT s1.Id
    FROM Stadium s1,Stadium s2,Stadium s3
    WHERE (s1.Id=s2.Id-1 AND s2.Id=s3.Id-1) AND
    (s1.people>=100 and s2.people>=100 and s3.people>=100) )
    UNION 
    (SELECT s2.Id
    FROM Stadium s1,Stadium s2,Stadium s3
    WHERE (s1.Id=s2.Id-1 AND s2.Id=s3.Id-1) AND
    (s1.people>=100 and s2.people>=100 and s3.people>=100) )
    UNION
    (SELECT s3.Id
    FROM Stadium s1,Stadium s2,Stadium s3
     WHERE (s1.Id=s2.Id-1 AND s2.Id=s3.Id-1) AND
    (s1.people>=100 AND s2.people>=100 AND s3.people>=100))
    ORDER BY visit_date ASC

2.使用基本语法查询
在表 stadium 中查询人流量超过 100 的记录，将查询结果与其自身的临时表连接，再使用 WHERE 子句获得满足条件的记录。
第一步：查询人流量超过 100 的记录，然后将结果与其自身的临时表连接。
第二步： 筛选出连续记录大于等于三条的行，
表 t1，t2 和 t3 相同，需要考虑添加哪些条件能够得到想要的结果。以 t1 为例，它有可能是连续三天的第 1 天，第 2 天，或第 3 天。

SELECT DISTINCT S1.*
FROM  Stadium S1, Stadium S2, Stadium S3
WHERE S1.people >= 100 AND S2.people >= AND S3.people >= 100
     AND
    (
        (S1.id - S2.id = 1 AND S1.id - S3.id = 2 AND S2.id - S3.id =1)-- S1, S2, S3
        OR
        (S2.id - S1.id = 1 AND S2.id - S3.id = 2 AND S1.id - S3.id =1)-- S2, S1, S3
        OR
        (S3.id - S2.id = 1 AND S2.id - S1.id =1 AND S3.id - S1.id = 2)-- S3, S2, S1
    )
ORDER BY  S1.id