[SQL]LeetCode197. 上升的温度 | Rising Temperature
★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
➤微信公众号：山青咏芝（shanqingyongzhi）
➤博客园地址：山青咏芝（https://www.cnblogs.com/strengthen/）
➤GitHub地址：https://github.com/strengthen/LeetCode
➤原文地址：https://www.cnblogs.com/strengthen/p/10180555.html
➤如果链接不是山青咏芝的博客园地址，则可能是爬取作者的文章。
➤原文已修改更新！强烈建议点击原文地址阅读！支持作者！支持原创！
★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
SQL架构
1 Create table If Not Exists Weather (Id int, RecordDate date, Temperature int) 2 Truncate table Weather 3 insert into Weather (Id, RecordDate, Temperature) values ('1', '2015-01-01', '10') 4 insert into Weather (Id, RecordDate, Temperature) values ('2', '2015-01-02', '25') 5 insert into Weather (Id, RecordDate, Temperature) values ('3', '2015-01-03', '20') 6 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
1 # Write your MySQL query statement below 2 SELECT t.id 3 FROM (SELECT i.*, 4 IF(@last_date + interval 1 day = RecordDate and @last_temp < Temperature, 1, 0) AS rownum, 5 @last_temp := Temperature, 6 @last_date := RecordDate 7 FROM (select @last_date := null, @last_temp := null) AS _init 8 , Weather i 9 ORDER BY i.RecordDate) AS t 10 WHERE t.rownum = 1;
166ms
1 # Write your MySQL query statement below 2 select b.keepId as Id 3 from (select if(Temperature > @prevTemp and datediff(RecordDate, @prevDate) = 1, a.Id, NULL) as keepId, 4 @prevTemp := Temperature, @prevDate := RecordDate 5 from (select * from Weather order by RecordDate) a, 6 (select @prevTemp := NULL, @prevDate := NULL) init) b 7 where keepId is not NULL
169ms
1 # Write your MySQL query statement below 2 3 SELECT Id 4 FROM 5 ( SELECT Id, Temperature, 6 @Higher := If(@Pretemp < Temperature AND 1 = DATEDIFF(RecordDate,@Predate), 1, 0) h, 7 @Predate := RecordDate predate, 8 @Pretemp := Temperature pretemp 9 FROM (SELECT @Higher := 0, @Pretemp := 101, @Predate := 1000-01-01) w1, 10 (SELECT * FROM Weather ORDER BY RecordDate) w2 11 ) t WHERE t.h = 1
170ms
1 # Write your MySQL query statement below 2 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;
