Given a Weather
table, write a SQL query to find all dates' Ids with higher temperature compared to its previous (yesterday's) dates.
+---------+------------+------------------+ | Id(INT) | Date(DATE) | Temperature(INT) | +---------+------------+------------------+ | 1 | 2015-01-01 | 10 | | 2 | 2015-01-02 | 25 | | 3 | 2015-01-03 | 20 | | 4 | 2015-01-04 | 30 | +---------+------------+------------------+
For example, return the following Ids for the above Weather table:
+----+ | Id | +----+ | 2 | | 4 | +----+when we use inner join,left join and right join we need to use Key Word On 后面加一些条件,select 出满足这些条件的表.
for example when use the following butch of code
select * from Weather as w1 inner join Weather as w2
it will show you results , the previous three columns is w1 and the latest three columns is w2. then we add some conditions to find results.
on TO_DAYS(w1.Date) = TO_DAYS(w2.Date) + 1 and w1.Temperature > w2.Temperature;
mysql> SELECT TO_DAYS('2009-05-18');
+-----------------------+
| TO_DAYS('2009-05-18') |
+-----------------------+
| 733910 |
+-----------------------+
1 row in set (0.03 sec)
+----+------------+-------------+----+------------+-------------+
| Id | Date | Temperature | Id | Date | Temperature |
+----+------------+-------------+----+------------+-------------+
| 1 | 2015-01-01 | 10 | 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 | 1 | 2015-01-01 | 10 |
| 3 | 2015-01-03 | 20 | 1 | 2015-01-01 | 10 |
| 4 | 2015-01-04 | 30 | 1 | 2015-01-01 | 10 |
| 1 | 2015-01-01 | 10 | 2 | 2015-01-02 | 25 |
| 2 | 2015-01-02 | 25 | 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 | 2 | 2015-01-02 | 25 |
| 4 | 2015-01-04 | 30 | 2 | 2015-01-02 | 25 |
| 1 | 2015-01-01 | 10 | 3 | 2015-01-03 | 20 |
| 2 | 2015-01-02 | 25 | 3 | 2015-01-03 | 20 |
| 3 | 2015-01-03 | 20 | 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 | 3 | 2015-01-03 | 20 |
| 1 | 2015-01-01 | 10 | 4 | 2015-01-04 | 30 |
| 2 | 2015-01-02 | 25 | 4 | 2015-01-04 | 30 |
| 3 | 2015-01-03 | 20 | 4 | 2015-01-04 | 30 |
| 4 | 2015-01-04 | 30 | 4 | 2015-01-04 | 30 |
+----+------------+-------------+----+------------+-------------+
so the result is
select w1.Id from Weather as w1 inner join Weather as w2
on TO_DAYS(w1.Date) = TO_DAYS(w2.Date) + 1 and w1.Temperature > w2.Temperature;