SQL -4

7.上升的温度

需求:编写一个 SQL 查询,来查找与之前(昨天的)日期相比温度更高的所有日期的 Id。

结果展示:

Id
2
4

建表语句:

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 1Weather (Id, RecordDate, Temperature) values (4, '2015-01-04', 30);

方法1:

select 
	w.id as id,
from 
	Weather w1
join 
	Weather w2
on 
	datadiff(w1.RecordDate,w2.RecordDate) = 1
and w1.Temperature > w2.Temperature;

方法2:

select 
	id 
from 
	(select
    	id,
   		RecordDate,
    	Temperature,
    	lag(RecordDate,1,9999-99-99) over(order by RecordDate) yd,
    	lag(Temperature,1,999) over(order by RecordDate) yt
     from 
     	Weather
    ) tmp
where Temperature > yt and datediff(RecordDatam,yd) = 1;
作者:yuexiuping
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利.
posted @ 2021-06-14 21:42  yuexiuping  阅读(15)  评论(0编辑  收藏  举报