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;
@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
SELECT today.*, yesterday.* FROM stadium today LEFT JOIN stadium yesterday ON today.id - 1 = yesterday.id;
LEFT JOIN ... ON ... today.id - 1 = yesterday.id; means we join field
people of today and field
people of yesterday into one row, the results are as followed:
We don't have any record of the day before
2017-01-01, so the last row left with three
nulls. 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:
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
p2 means previous N days for short, and
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.
And that's what the
WHERE statement does.
Hope it helps! :-P