SQL—day3
题目
编写一个 SQL 查询,来查找与之前(昨天的)日期相比温度更高的所有日期的 id 。
查询结果格式如下例:
Weather
+----+------------+-------------+
| id | recordDate | Temperature |
+----+------------+-------------+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
+----+------------+-------------+
Result table:
+----+
| id |
+----+
| 2 |
| 4 |
+----+
2015-01-02 的温度比前一天高(10 -> 25)
2015-01-04 的温度比前一天高(20 -> 30)
预备知识
①
Mysql中有DATEDIFF函数,求两个日期的天数差集
DATEDIFF(被减数, 减数)
例如:
SELECT DATEDIFF('2020-12-14','2020-12-13') AS DiffDate
1
SELECT DATEDIFF('2020-12-13','2020-12-14') AS DiffDate
1
②
@ 定义用户变量
@@ 引用或定义全局变量
= 只有在set和update时才是和:=一样,赋值的作用,其它都是等于的作用
:= 不只在set和update时时赋值的作用,在select也是赋值的作用
因此用变量实现行号时,必须用:=
思路
1. weather表格做自联结,连接条件需要同时满足’温度关系‘&’日期差异‘,从链接后的表格查询出所有ID
2. 定义日期、温度变量,循环利用布尔标志符判断
代码
基本查询
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 w.*,
@curd := w.RecordDate,
@curt := w.Temperature,
@isH := if(datediff(@curd,@pred) = 1 and @curt > @pret,1,0) as r,
@pret := @curt,
@pred := @curd
-- @pret := w.Temperature, 与上方等价
-- @pred := w.RecordDate 与上方等价
from Weather as w
order by w.RecordDate
) t
where
t.r = 1
来源:力扣(LeetCode)
链接:https://leetcode-cn.com/problems/rising-temperature