SELECT t.* FROM stadium t
LEFT JOIN stadium p1 ON t.id  1 = p1.id
LEFT JOIN stadium p2 ON t.id  2 = p2.id
LEFT JOIN stadium n1 ON t.id + 1 = n1.id
LEFT JOIN stadium n2 ON t.id + 2 = n2.id
WHERE (t.people >= 100 AND p1.people >= 100 AND p2.people >= 100)
OR (t.people >= 100 AND n1.people >= 100 AND n2.people >= 100)
OR (t.people >= 100 AND n1.people >= 100 AND p1.people >= 100)
ORDER BY id;
solution using join


@liupangzi Can you please explain the JOINS and why there is a specific combination in the WHERE clause? Thanks.

Let's start from a basic
LEFT JOIN
:SELECT today.*, yesterday.* FROM stadium today LEFT JOIN stadium yesterday ON today.id  1 = yesterday.id;
The statement
LEFT JOIN ... ON ... today.id  1 = yesterday.id;
means we join fieldpeople
of today and fieldpeople
of yesterday into one row, the results are as followed:id date people id date people 2 20170102 109 1 20170101 10 3 20170103 150 2 20170102 109 4 20170104 99 3 20170103 150 5 20170105 145 4 20170104 99 6 20170106 1455 5 20170105 145 7 20170107 199 6 20170106 1455 8 20170108 188 7 20170107 199 1 20170101 10 (null) (null) (null) We don't have any record of the day before
20170101
, so the last row left with threenull
s. And this SQL helps us to find two consecutive days of which people more than 100(JOIN
table yesterday and tomorrow):SELECT today.*, yesterday.* FROM stadium today LEFT JOIN stadium yesterday ON today.id  1 = yesterday.id LEFT JOIN stadium tomorrow ON today.id + 1 = tomorrow.id WHERE (today.people >= 100 AND yesterday.people >= 100) OR (today.people >= 100 AND tomorrow.people >= 100);
Similarly, this can be applied to 3 consecutive days by table
today
joining 4 tables:yesterday
,tomorrow
,the_day_before_yesterday
,the_day_after_tomorrow
:SELECT t.* FROM stadium t LEFT JOIN stadium p1 ON t.id  1 = p1.id LEFT JOIN stadium p2 ON t.id  2 = p2.id LEFT JOIN stadium n1 ON t.id + 1 = n1.id LEFT JOIN stadium n2 ON t.id + 2 = n2.id
Here
p1
/p2
means previous N days for short, andn1
/n2
means next n days for short,t
for today.After we get record from SQL above, filter records(there are three ways of 3 consecutive days of which more than 100 people, as the table I list below) with an additional
ORDER BY
should meet the requirement.p2 p1 t n1 n2 true true true    true true true    true true true And that's what the
WHERE
statement does.Hope it helps! :P