[SQL]LeetCode197. 上升的温度 | Rising Temperature

★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
➤微信公众号：山青咏芝（shanqingyongzhi）
➤博客园地址：山青咏芝（https://www.cnblogs.com/strengthen/）
➤GitHub地址：https://github.com/strengthen/LeetCode
➤原文地址：https://www.cnblogs.com/strengthen/p/10180555.html 
➤如果链接不是山青咏芝的博客园地址，则可能是爬取作者的文章。
➤原文已修改更新！强烈建议点击原文地址阅读！支持作者！支持原创！
★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★

热烈欢迎，请直接点击！！！

进入博主App Store主页，下载使用各个作品！！！

注：博主将坚持每月上线一个新app！！！

SQL架构

Create table If Not Exists Weather (Id int, RecordDate date, Temperature int)
Truncate table Weather
insert into Weather (Id, RecordDate, Temperature) values ('1', '2015-01-01', '10')
insert into Weather (Id, RecordDate, Temperature) values ('2', '2015-01-02', '25')
insert into Weather (Id, RecordDate, Temperature) values ('3', '2015-01-03', '20')
insert into Weather (Id, RecordDate, Temperature) values ('4', '2015-01-04', '30')

---

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

---

给定一个 Weather 表，编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 Id。

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

例如，根据上述给定的 Weather 表格，返回如下 Id:

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

---

163ms

# Write your MySQL query statement below
SELECT t.id
FROM (SELECT i.*,
             IF(@last_date + interval 1 day = RecordDate and @last_temp < Temperature, 1, 0) AS rownum,
             @last_temp := Temperature,
             @last_date := RecordDate
      FROM (select @last_date := null, @last_temp := null) AS _init
             , Weather i
      ORDER BY i.RecordDate) AS t
WHERE t.rownum = 1;

---

166ms

# Write your MySQL query statement below
select b.keepId as Id
from (select if(Temperature > @prevTemp and datediff(RecordDate, @prevDate) = 1, a.Id, NULL) as keepId, 
      @prevTemp := Temperature, @prevDate := RecordDate
      from (select * from Weather order by RecordDate) a,
      (select @prevTemp := NULL, @prevDate := NULL) init) b
where keepId is not NULL

---

169ms

# Write your MySQL query statement below

SELECT Id 
FROM 
   ( SELECT Id, Temperature,
          @Higher   := If(@Pretemp < Temperature AND 1 =  DATEDIFF(RecordDate,@Predate), 1, 0) h,
          @Predate  := RecordDate predate,
          @Pretemp  := Temperature pretemp
     FROM  (SELECT @Higher := 0, @Pretemp := 101, @Predate := 1000-01-01) w1,
           (SELECT * FROM Weather ORDER BY RecordDate) w2 
     ) t WHERE t.h = 1

---

170ms

# Write your MySQL query statement below
select f2.Id from (select f.Id, if(Temperature>@prev and datediff(RecordDate, @prevDate)=1, 1, 0) as rising, @prev:=Temperature, @prevDate:=RecordDate from (select Id, RecordDate, Temperature from (select * from Weather order by RecordDate asc) ordered, (select @rising:=NULL, @prev:=NULL, @prevDate:=NULL) tmp) f) f2 where f2.rising = 1 order by f2.Id;