高频 SQL 50 题（基础版）_197. 上升的温度
表： Weather
id 是该表具有唯一值的列。
没有具有相同 recordDate 的不同行。
该表包含特定日期的温度信息
编写解决方案，找出与之前（昨天的）日期相比温度更高的所有日期的 id 。
返回结果 无顺序要求 。
结果格式如下例子所示。
波峰波谷处理
SELECT id
FROM
(
SELECT id, temperature, recordDate,
LAG(temperature, 1) OVER (PARTITION BY ids ORDER BY recordDate) AS lag_temp,
LAG(recordDate, 1) OVER (PARTITION BY ids ORDER BY recordDate) AS lag_date
FROM (
SELECT 1 AS ids, id, recordDate, temperature
FROM Weather
) AS subquery
) AS qb
WHERE temperature > lag_temp
AND recordDate = DATE_ADD(lag_date, INTERVAL 1 DAY)
两表联结
select w1.id from Weather w1, Weather w2
where w1.recordDate - interval 1 day = w2.recordDate
and w1.Temperature > w2.Temperature