LeetCode: 197. 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) | 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 | +----+

解题思路

The MySQL DATEDIFF function calculates the number of days between two DATE, DATETIME, or TIMESTAMPvalues.
The syntax of the MySQL DATEDIFF function is as follows:

DATEDIFF(date_expression_1,date_expression_2);

参考文献：MySQL DATEDIFF Function

AC 代码

SELECT W1.Id AS Id FROM Weather AS w1, Weather AS w2 WHERE DATEDIFF(w1.RecordDate, w2.RecordDate)=1 AND w1.Temperature > w2.Temperature