[MySQL][LeetCode][Easy] 197. Rising Temperature

心得

題目要求找出溫度比前一天高的資料,這裡用了DATE_SUBTO_DAYS兩種方式來解答。

題目

Given a Weather table, write a SQL query to find all dates’ Ids with higher temperature compared to its previous (yesterday’s) dates.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14

+----+
>
> | Id |
>
> +----+
>
> | 2 |
>
> | 4 |
>
> +----+
>
>

答案

  1. Sub Query
1
2
3
4
5
6
7

# Write your MySQL query statement below
SELECT a.Id
FROM Weather AS a
WHERE ( SELECT z.Temperature
FROM Weather AS z
WHERE DATE_SUB(a.Date, INTERVAL 1 DAY) = z.Date) < a.Temperature
  1. Join
1
2
3
4
5
6
7

# Write your MySQL query statement below
SELECT a.Id
FROM Weather AS a
JOIN Weather AS b
ON DATE_SUB(a.Date, INTERVAL 1 DAY) = b.Date
WHERE a.Temperature > b.Temperature
  1. TO_DAYS
1
2
3
4
5
6
7

# Write your MySQL query statement below
SELECT a.Id
FROM Weather AS a
JOIN Weather AS b
ON TO_DAYS(a.Date) - TO_DAYS(b.Date) = 1
WHERE a.Temperature > b.Temperature

 
參考:

  1. DATE_ADD() 與 DATE_SUB() 日期的加法與減法
  2. Mysql日期和時間函數不求人