Import Data:
Create table If Not Exists Stadium (id int, visit_date DATE NULL, people int);
Truncate table Stadium;
insert into Stadium (id, visit_date, people) values ('1', '2017-01-01', '10');
insert into Stadium (id, visit_date, people) values ('2', '2017-01-02', '109');
insert into Stadium (id, visit_date, people) values ('3', '2017-01-03', '150');
insert into Stadium (id, visit_date, people) values ('4', '2017-01-04', '99');
insert into Stadium (id, visit_date, people) values ('5', '2017-01-05', '145');
insert into Stadium (id, visit_date, people) values ('6', '2017-01-06', '1455');
insert into Stadium (id, visit_date, people) values ('7', '2017-01-07', '199');
insert into Stadium (id, visit_date, people) values ('8', '2017-01-09', '188');
Table: Stadium
+ --------------- + --------- +
| Column Name | Type |
+ --------------- + --------- +
| id | int |
| visit_date | date |
| people | int |
+ --------------- + --------- +
visit_date is the primary key of the table
The daily flow of people information is recorded in these three columns of information: sequence number (id), date (visit_date), flow of people (people)
There is only one row of records per day, and the date increases with the increase of id
Write a SQL query to find three or more rows with consecutive ids where the number of people in each row is greater than or equal to 100.
Returns a table of results sorted by visit_date in ascending order.
The query result format is as follows.
Example 1:
enter:
Stadium tables:
+ —— + ———— + ———– +
| id | visit_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-09 | 188 |
+ —— + ———— + ———– +
output:
+ —— + ———— + ———– +
| id | visit_date | people |
+ —— + ———— + ———– +
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-09 | 188 |
+ —— + ———— + ———– +
explain:
The four rows with ids 5, 6, 7, and 8 have consecutive ids, and each row has records of the number of people >= 100.
Note that even though the visit_dates of rows 7 and 8 are not consecutive, the output should include row 8 because we only need to consider records with consecutive ids.
The rows with id 2 and 3 are not output, because at least three consecutive id records are required.
Problem solving:
Method: Using JOIN and WHERE clauses
Idea
Query the records with more than 100 people in the table stadium, connect the query results with its own temporary table, and then use the WHERE clause to obtain the records that meet the conditions.
Algorithms
Step 1: Query records with more than 100 people, and then join the result with its own temporary table.
select distinct t1.* from stadium t1, stadium t2, stadium t3 where t1.people >= 100 and t2.people >= 100 and t3.people >= 100 ;
| id | date | people | id | date | people | id | date | people |
|—-|————|——-|—-|————|—- —-|—-|————|——–|
2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 |
3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 |
5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 |
6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 |
7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 |
8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 |
2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 |
3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 |
5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 |
6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 |
7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 |
8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 |
2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 |
3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 |
5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 |
6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 |
7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 |
8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 |
2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 |
3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 |
5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 |
6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 |
7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 |
8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 |
2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 |
3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 |
5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 |
6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 |
7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 |
8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 |
2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 |
3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 |
5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 |
6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 |
7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 |
8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 |
2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 |
3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 |
5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 |
6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 |
7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 |
8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 |
2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 |
3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 |
5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 |
6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 |
7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 |
8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 |
2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 |
3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 |
5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 |
6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 |
7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 |
8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 |
2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 |
3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 |
5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 |
6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 |
7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 |
8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 |
2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 |
3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 |
5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 |
6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 |
7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 |
8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 |
2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 |
3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 |
5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 |
6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 |
7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 |
8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 |
2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 |
3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 |
5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 |
6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 |
7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 |
8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 |
2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 |
3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 |
5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 |
6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 |
7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 |
8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 |
2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 |
3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 |
5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 |
6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 |
7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 |
8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 |
2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 |
3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 |
5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 |
6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 |
7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 |
8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 |
2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 |
3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 |
5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 |
6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 |
7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 |
8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 |
2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 |
3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 |
5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 |
6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 |
7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 |
8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 |
2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 |
3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 |
5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 |
6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 |
7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 |
8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 |
2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 |
3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 |
5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 |
6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 |
7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 |
8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 |
2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 |
3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 |
5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 |
6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 |
7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 |
8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 |
2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 |
3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 |
5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 |
6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 |
7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 |
8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 |
2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 |
3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 |
5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 |
6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 |
7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 |
8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 |
2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 |
3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 |
5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 |
6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 |
7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 |
8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 |
2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 |
3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 |
5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 |
6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 |
7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 |
8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 |
2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 |
3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 |
5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 |
6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 |
7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 |
8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 |
2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 |
3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 |
5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 |
6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 |
7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 |
8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 |
2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 |
3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 |
5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 |
6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 |
7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 |
8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 |
2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 |
3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 |
5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 |
6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 |
7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 |
8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 |
2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 |
3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 |
5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 |
6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 |
7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 |
8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 |
2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 |
3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 |
5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 |
6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 |
7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 |
8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 |
2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 |
3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 |
5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 |
6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 |
7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 |
8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 |
2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 |
3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 |
5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 |
6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 |
7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 |
8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 |
2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 |
3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 |
5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 |
6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 |
7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 |
8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 |
2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 |
3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 |
5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 |
6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 |
7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 |
8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 |
2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 |
3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 |
5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 |
6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 |
7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 |
8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 |
There were 6 days with more than 100 foot traffic, 216 (666) records after Cartesian product.
The first 3 columns are from table t1, the middle 3 columns are from table t2, and the last 3 columns are from table t3.
Tables t1, t2 and t3 are the same, you need to consider which conditions to add to get the desired results. Taking t1 as an example, it could be day 1, day 2, or day 3 of the peak period.
t1 is day 1 of peak period: (t1.id – t2.id = 1 and t1.id – t3.id = 2 and t2.id – t3.id =1) — t1, t2, t3
t1 is day 2 of peak period: (t2.id – t1.id = 1 and t2.id – t3.id = 2 and t1.id – t3.id =1) — t2, t1, t3
t1 is day 3 of peak period: (t3.id – t2.id = 1 and t2.id – t1.id =1 and t3.id – t1.id = 2) — t3, t2, t1
select t1.*
from stadium t1, stadium t2, stadium t3
where t1.people >= 100 and t2.people >= 100 and t3.people >= 100
and
(
(t1.id - t2.id = 1 and t1.id - t3.id = 2 and t2.id - t3.id =1) -- t1, t2, t3
or
(t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id =1) -- t2, t1, t3
or
(t3.id - t2.id = 1 and t2.id - t1.id =1 and t3.id - t1.id = 2) -- t3, t2, t1
)
;
| id | date | people |
|—-|————|——-|
| 7 | 2017-01-07 | 199 |
| 6 | 2017-01-06 | 1455 |
| 8 | 2017-01-08 | 188 |
| 7 | 2017-01-07 | 199 |
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
You can see that there are duplicate records in the query results, and then use DISTINCT to remove them.
select distinct t1.*
from stadium t1, stadium t2, stadium t3
where t1.people >= 100 and t2.people >= 100 and t3.people >= 100
and
(
(t1.id - t2.id = 1 and t1.id - t3.id = 2 and t2.id - t3.id =1) -- t1, t2, t3
or
(t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id =1) -- t2, t1, t3
or
(t3.id - t2.id = 1 and t2.id - t1.id =1 and t3.id - t1.id = 2) -- t3, t2, t1
)
order by t1.id
;
Source: LeetCode
Link: https://leetcode.cn/problems/human-traffic-of-stadium
The copyright belongs to Lingkou Network. For commercial reprints, please contact the official authorization, for non-commercial reprints, please indicate the source.