MYSQL实现"上升的温度"的四种方法

给定一个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结果

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

1：利用DATEDIFF()方法（参考：https://www.cnblogs.com/grandyang/p/5371438.html）

DATEDIFF() 函数返回两个日期之间的天数。

由于Id的排列未必是按顺序的，所以要找前一天就得根据日期来找，可以使用MySQL的函数Datadiff来计算两个日期的差值。限制条件是温度高且日期差1

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

2：利用DATA_SUB()方法

DATE_SUB() 函数从日期减去指定的时间间隔

SELECT W1.Id 
FROM Weather AS W1, Weather AS W2
WHERE DATE_SUB(W1.RecordDate, INTERVAL 1 DAY)=W2.RecordDate AND W1.Temperature>W2.Temperature

 

3：利用DATA_ADD()方法

SELECT W1.Id 
FROM Weather AS W1, Weather AS W2
WHERE W1.RecordDate=DATE_ADD(W2.RecordDate, INTERVAL 1 DAY) AND W1.Temperature>W2.Temperature

4：利用TO_DAYS()方法（参考：https://blog.csdn.net/qinkaiyuan94/article/details/78649549、https://www.cnblogs.com/grandyang/p/5371438.html）

to_days(date) -将日期类型转为数字类型

from_days(date)-将天数转为日期

SELECT W1.Id 
FROM Weather AS W1, Weather AS W2
WHERE TO_DAYS(W1.RecordDate)-TO_DAYS(W2.RecordDate)=1 AND W1.Temperature>W2.Temperature

算法题来自：https://leetcode-cn.com/problems/rising-temperature/description/