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

描述

找出 Temperature 比前一天大的 Id

代码

-- 方法一

select 
	a.Id 
from 
	Weather a, Weather b 
where 
	a.Temperature > b.Temperature and to_days(a.Date) - to_days(b.Date) = 1

-- 方法二

select 
	a.Id 
from 
	Weather a, Weather b 
where 
	a.Temperature > b.Temperature and date_sub(a.Date, interval 1 day) = b.Date

-- 方法三

select 
	a.Id 
from 
	Weather a, Weather b 
where 
	a.Temperature > b.Temperature and datediff(a.Date, b.Date) = 1