day1 上升的溫度--三種方法

題目

題目來源：上升的溫度

描述：查詢與之前（昨天）日期相比溫度更高的所有日期的 id 。

使用的是 MySQL8.0，沒有在 MySQL5.6 版本中測驗過，不保證正確。

create table weather (
    id int primary key auto_increment,
    recordDate date,
    temperature int
);

insert into weather(recordDate, temperature) values
('2015-01-01', 10),
('2015-01-02', 25),
('2015-01-03', 20),
('2015-01-04', 30);

SQL：方法一

select weather.id from weather join weather w1
on datediff(weather.recordDate, w1.recordDate) = 1
and weather.temperature > w1.temperature;

解析

只有一張表，現在要找出今天溫度比昨天溫度高的日期 id 。

所以需要用自連線，也就是把 weather 和 weather 進行自身連線。

在自連之後，需要將自連後的表取個別名 w1 ，如果不取別名的話，兩個 weather 表名會衝突。這裡把 weather 作為今天表， w1 作為昨天表。

兩表自連之後需要有連線條件，連線條件是 今天和昨天的日期。

MySQL 提供了datediff 函式，用來比較兩個日期之間的時間差，如果兩個時間之間相差 1 天，那麼就是今天和做題。

最後在篩選出今天的溫度高於昨天溫度的資料。

SQL：方法二

select weather.id from weather join weather w1 
on weather.recordDate = adddate(w1.recordDate, interval 1 day) 
and weather.temperature > w1.temperature;

解析

思路和方法一的思路是一樣的，區別在於計算今天和昨天的方法不一樣。

這裡使用 MySQL 提供的 adddate 函式。這個函式是將日期函式一個規律進行偏移。

SQL：方法三

select id from (
    select
    temperature, 
    recordDate ,
    lead(id, 1) over(order by recordDate) as id,
    lead(recordDate, 1) over(order by recordDate) as 'nextDate',
    lead(temperature, 1) over(order by recordDate) as 'nextTemp'
    from weather
) temp
where nextTemp > temperature and datediff(nextDate, recordDate) = 1;

解析

使用視窗函式 lead ，它是從後往前偏移，偏移量為 1 天。

select
    temperature, 
    recordDate ,
    lead(id, 1) over(order by recordDate) as nextId,
    lead(recordDate, 1) over(order by recordDate) as 'nextDate',
    lead(temperature, 1) over(order by recordDate) as 'nextTemp'
from weather;

這裡說一下，視窗函式還有一個 lag 是從前往後偏移的，用法和 lead 是一樣的。這裡就用 lead 來舉例。

前三列是 weather 原資料，後三列是使用視窗函式 lead 算出來的資料。

為什麼是偏移 1 呢？

因為比較的是今天和昨天，而且這裡日期是連續的，所以用 1 。

然後查詢出來的資料作為一個臨時表 temp 。

篩選條件就是 nextTemp > temperature ，最後使用 datediff 比較兩個日期差可寫可不寫，因為這裡日期是連續的。

更多解題參考：https://github.com/astak16/bl...