X 市建了一个新的体育馆，每日人流量信息被记录在这三列信息中：序号 (id)、日期 (date)、 人流量 (people)。
请编写一个查询语句，找出高峰期时段，要求连续三天及以上，并且每天人流量均不少于100。
例如，表 stadium：
+------+------------+-----------+
| id | 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-08 | 188 |
+------+------------+-----------+
对于上面的示例数据，输出为：
+------+------------+-----------+
| id | date | people |
+------+------------+-----------+
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-08 | 188 |
+------+------------+-----------+
Note:
每天只有一行记录，日期随着 id 的增加而增加。
思路：
这一题同样很简单，和上一题连续数字是非常类似的，首先做一轮查询，记录连续大于100的天数，默认为1，如果下一天符合则+1，否则重置；同时给一个标识，用于标识出当前连续段的特征值。
得到以上结果，我们就可以查出所有连续次数大于等于3的的天数记录及其标识，这样也可以得到需要的结果了。
SELECT
e.id,
e.date,
e.people
FROM
(SELECT
id,
s.date,
@sdate1 := (
CASE
WHEN s.people >= 100
THEN @sdate1
ELSE @sdate1 + 1
END
) AS sdate,
s.people
FROM
stadium s,
(SELECT
@sdate1 := 0) b) e,
(SELECT
c.sdate
FROM
(SELECT
id,
s.date,
@sdate := (
CASE
WHEN s.people >= 100
THEN @sdate
ELSE @sdate + 1
END
) AS sdate,
s.people
FROM
stadium s,
(SELECT
@sdate := 0) b) c
GROUP BY c.sdate
HAVING COUNT(c.sdate) > 3) d
WHERE d.sdate = e.sdate
AND e.people >= 100
由于mysql没有with as，这里复用部分的子查询就显得很长很难看了。
我用的方法是比较死板的遍历查找，只能击败88.48%，其他提交的普遍采用以id+1,id+2为条件做全表查询，更巧妙吧。
SELECT DISTINCT
s1.*
FROM
stadium AS s1,
stadium AS s2,
stadium AS s3
WHERE (
(s1.id + 1 = s2.id
AND s1.id + 2 = s3.id)
OR (s1.id - 1 = s2.id
AND s1.id + 1 = s3.id)
OR (s1.id - 2 = s2.id
AND s1.id - 1 = s3.id)
)
AND s1.people >= 100
AND s2.people >= 100
AND s3.people >= 100
ORDER BY s1.id
网友评论