Haiyang Yu's Blog
0%

Leetcode 197 Rising Temperature

发表于 更新于 分类于 Valine：
本文字数： 1.1k 阅读时长 ≈ 1 分钟

Table: Weather

1
2
3
4
5
6
7
8
9
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| recordDate    | date    |
| temperature   | int     |
+---------------+---------+
id is the primary key for this table.
This table contains information about the temperature in a certain day.

Write an SQL query to find all dates’ id with higher temperature compared to its previous dates (yesterday).

Return the result table in any order.

The query result format is in the following example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Weather
+----+------------+-------------+
| id | recordDate | Temperature |
+----+------------+-------------+
| 1  | 2015-01-01 | 10          |
| 2  | 2015-01-02 | 25          |
| 3  | 2015-01-03 | 20          |
| 4  | 2015-01-04 | 30          |
+----+------------+-------------+

Result table:
+----+
| id |
+----+
| 2  |
| 4  |
+----+
In 2015-01-02, temperature was higher than the previous day (10 -> 25).
In 2015-01-04, temperature was higher than the previous day (30 -> 20).

JOIN

思路是连接两个表, 第n天的信息去和第n-1天的信息连接. 如果第n-1天的温度小于第n天的温度, 那么就返回这个值.否则不返回这个值

注意date类型的加减法是用date_add()实现的

1
2
# Write your MySQL query statement below
SELECT t1.id FROM Weather AS t1, Weather AS t2 WHERE t1.recordDate = date_add(t2.recordDate, interval 1 day) AND t1.Temperature > t2.Temperature;