【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.

解答：关于时间的Mysql的函数：

1、TO_DAYS(date):给定一个日期data，返回从年份0开始的天数

2、DATEDIFF()：返回两个日期之间的天数，左边的减去右边的数值

3、SUBDATE()：实现日期的加减；

4、DATE_ADD() 、DATE_SUB()：

语法：DATE_SUB(date,INTERVAL expr type)，

示例：SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate FROM Orders

注意，必须得有INTERVAL

1）

SELECT w2.Id AS Id
FROM Weather w1, Weather w2 
WHERE TO_DAYS(w1.Date)+1 = TO_DAYS(w2.Date) AND w1.Temperature < w2.Temperature

2）

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

3)

SELECT w1.Id AS Id
FROM Weather w1, Weather w2 
WHERE SUBDATE(w1.Date,1) = w2.Date AND w1.Temperature > w2.Temperature

4)

SELECT w1.Id AS Id
FROM Weather w1, Weather w2 
WHERE DATE_SUB(w1.Date,INTERVAL 1 DAY) = w2.Date AND w1.Temperature > w2.Temperature