197. Rising Temperature(简单)
给定一个 weather 表,编写一个SQL语句查找与之前(昨天的)日期相比温度更高的所有日期的id
+---------+------------------+------------------+
| 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 |
+---------+------------------+------------------+
预期结果如下:
+----+
| Id |
+----+
| 2 |
| 4 |
+----+
解法一:先用 to_days()函数,确定昨天,然后再比较温度的高低
select w1.Id
from weather as w1,weather as w2
where w1.Temperature > w2.Temperature and to_days(w1.RecordDate)-to_days(w2.RecordDate)=1;
函数 to_days( ) :返回从0000年(公元1年)至 当前日期(所给参数)的总天数
737218 即0000年至2018-06-08的天数
解法二:先用datediff() 函数,确定昨天,再比较温度
select w1.Id
from weather as w1,weather as w2
where w1.Temperature > w2.Temperature and datediff(w1.RecordDate,w2.RecordDate)=1;
函数datediff( ) : 返回两个日期之间的天数