197. Rising Temperature

最新推荐文章于 2024-10-07 22:24:34 发布

原创最新推荐文章于 2024-10-07 22:24:34 发布 · 95 阅读

0 ·

CC 4.0 BY-SA版权

leetcode 专栏收录该内容

31 篇文章

订阅专栏

题目

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 |
±—+

代码

SELECT w1.Id FROM Weather w1,Weather w2
WHERE w1.Temperature > w2.Temperature
AND TO_DAYS(w1.RecordDate)-TO_DAYS(w2.RecordDate)=1

mysql的日期处理函数
TO_DAYS(date)给出一个日期 date，返回一个天数(从 0 年开始的天数)
所以TO_DAYS(w1.RecordDate)-TO_DAYS(w2.RecordDate)=1表示前一天
不带任何条件的自连结
SELECT w1.Id FROM Weather w1,Weather w2