Rising Temperature

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.

Example

±--------±-----------------±-----------------+
| Id(INT) | RecordDate(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 |
±—+

Solution

@奇怪下面的写法有的案例跑不过
# Write your MySQL query statement below
SELECT W1.Id FROM Weather W1
  JOIN Weather W2 ON DATEDIFF(W1.RecordDate, W2.RecordDate)=1
 WHERE W2.Temperature<W1.Temperature

SELECT W1.Id FROM Weather W1
  JOIN Weather W2 ON W2.RecordDate+1=W1.RecordDate
 WHERE W2.Temperature<W1.Temperature