Leetcode 197. Rising Temperature

197. Rising Temperature

原题目链接: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 |
+---------+------------------+------------------+

举个例子,在运行完你的查询脚本之后,上述Weather表格应该返回下面的Id:

+----+
| Id |
+----+
|  2 |
|  4 |
+----+

Solution

方法:采用JOIN语句和DATEDIFF()函数[Accepted]

Algorithm

MySQL采用DATEDIFF来比较两个日期类型的值。
因此,我们可以通过使用DATEDIFF()函数在将weather表与自身相连接的基础上获得所要求的结果。

MySQL

1
2
3
4
5
6
7
8
SELECT
weather.id AS 'Id'
FROM
weather
JOIN
weather w ON DATEDIFF(weather.Recorddate, w.Recorddate) = 1
AND weather.Temperature > w.Temperature
;