Rising Temperature



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 |
+----+

题意：找出记录中比前一天温度高的记录

用时1877ms

select id from Weather as w1 where  w1.temperature > (select temperature from Weather as w2 where w2.date = date_sub(w1.date, interval 1 day))

用时1290 ms 

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;
看来还是用连接查询要快些