首页 > itarticle > sql notes: leetcode#197 rising temperature

sql notes: leetcode#197 rising temperature

admin 11月 12, 2020 0

Problem

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

Analysis

Join two Weather tables to iterate all possible (today, yesterday) and filter with
today.Temperature > yesterday.Temperature.

Solution

1 2	SELECT t.Id FROM Weather AS t, Weather AS y WHERE DATEDIFF(t.RecordDate, y.RecordDate) = 1 AND t.Temperature > y.Temperature;

Link

197. Rising Temperature
(中文版) SQL 笔记: Leetcode#197 Rising Temperature