Rising Temperature

Rising Temperature

---

description:
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) | 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 |
+----+

---

这道题不难，但是要借助MySQL的相关时间函数才能解决，所以记录一下。
题意是让在Weather表当中找出当前那天比前一天温度高的那天的Id。
我刚开始没想那么多，直接用日期相减，这样写的：

# Write your MySQL query statement below
select w2.id from Weather w1 join Weather w2 on w1.recorddate=w2.recorddate-1
where w2.temperature > w1.temperature;

这样写果然就WA了，date类型的数据应该是不能直接相加减这样写，后面查了个函数DATEDIFF(a, b):计算a和b两个日期的差值a-b。用了这个函数后就过了。
Mycode:

# Write your MySQL query statement below
select w2.id from Weather w1 join Weather w2 on DATEDIFF(w2.recorddate, w1.recorddate) = 1
where w2.temperature > w1.temperature;

另外还有两个函数也介绍一下：
TO_DAYS(a): 用来将日期a换算成天数
SubDate(a,b): 用来将日期a减掉b天，基本和Date_Sub(a, b)一样用
因此，这道题也可以这样写：

# Write your MySQL query statement below
select w2.id from Weather w1 join Weather w2 on To_DAYS(w2.recorddate)=To_DAYS(w1.recorddate)+1
where w2.temperature > w1.temperature;

# Write your MySQL query statement below
select w2.id from Weather w1 join Weather w2 on SubDate(w2.recorddate, 1) = w1.recorddate
where w2.temperature > w1.temperature;

谢谢你的观看！