197. 上升的温度

最新推荐文章于 2023-07-04 09:55:57 发布

静听山水

最新推荐文章于 2023-07-04 09:55:57 发布

阅读量224

点赞数 1

分类专栏： SQL

本文链接：https://blog.csdn.net/qq_41081716/article/details/108876239

版权

SQL 专栏收录该内容

125 篇文章 3 订阅

订阅专栏

197. 上升的温度 - 力扣（LeetCode）

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')

正解：

select a.Id from  Weather as a join Weather as b 
on a.Temperature > b.Temperature 
and datediff(a.RecordDate,b.RecordDate) = 1

用下面这个，会出现一些错误，因为没有控制时间差恰好为一天，如果原始数据为连续日期则没问题。

select Id from (
select *,lag(Temperature,1,1000) over(order by RecordDate) t1 from 
(select *
from Weather order by RecordDate) a)b 
where Temperature>t1