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 WordOn 后面加一些条件，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;