Leetcode:Rising Temperature database

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;

To_DAYS is a function return the Integer

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;