Leetcode 197. Rising Temperature

197. Rising Temperature

Total Accepted: 18877 Total Submissions: 72552 Difficulty: Easy

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) | Date(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 |+----+

至此博主在这几天将leetcode上sql的13题除hard之外的11题全部一遍而且blog了。话说一题一个blog还是有气势，不过之前发的各种合集是为了横向对比，这样碰到一个系列的题就不必分别去每个题的解答了。

思路：

这个题有个小陷阱。大体上取出俩weather，然后对比它们的温度和id关系就可以了。陷阱是：如果我们直接用 a.date = b.date + 1，会有专门的test case过不了，这个情况就是月末和下月初的情况。网上的建议是使用一个函数，转换date成一个数，这样就不会发生月末的问题了。代码中有说明。

# Write your MySQL query statement below/*TO_DAYS(wt1.DATE) return the number of days between from year 0 to date DATETO_DAYS(wt1.DATE)-TO_DAYS(wt2.DATE)=1 check if wt2.DATE is yesterday respect to wt1.DATEalternatively:where DATE_SUB(a.Date,INTERVAL 1 DAY) = b.Date);*/select a.Id from Weather a, Weather bwhere (TO_DAYS(a.date)-TO_DAYS(b.date)=1) and (a.temperature > b.temperature);