解法一：临时表

SQL
create table weather(
    id int primary key ,
    recordData date ,
    temperature int
);

show tables;

select * from weather;

insert into weather values (1,'2015-01-01',10),(2,'2015-01-02',25),(3,'2015-01-03',20),(4,'2015-01-04',30);

select a.id 
from weather as a,weather as b 
where a.temperature > b.temperature 
and datediff(a.recordData,b.recordData) = 1;

解法二:自连接

sql
select a.id 
from weather as a join weather as b 
on datediff(a.recordData,b.recordData) = 1 
where a.temperature > b.temperature ;

总结

• datediff返回日期a,和b相差的天数。
• to_days('2015-01-01')返回从0000年到现在的天数。
• adddate('2015-01-01',INTERVAL 1 day)添加时间。
• timestampdiff

select timestampdiff(day,a,b); #计算两个时间相差的天数
select timestampdiff(hour,a,b); #计算两个时间相差的小时数
select timestampdiff(second,a,b); #计算两个时间相差的秒数

• data_sub(a,interval 1 day) a日期减去指定天数。