题目链接:https://leetcode-cn.com/problems/rising-temperature/
sql架构
表: Weather
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| recordDate | date |
| temperature | int |
+---------------+---------+
id 是这个表的主键
该表包含特定日期的温度信息
编写一个 SQL 查询,来查找与之前(昨天的)日期相比温度更高的所有日期的 id 。
返回结果 不要求顺序 。
查询结果格式如下例。
示例1
输入:
Weather 表:
+----+------------+-------------+
| id | recordDate | Temperature |
+----+------------+-------------+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
+----+------------+-------------+
输出:
+----+
| id |
+----+
| 2 |
| 4 |
+----+
解释:
2015-01-02 的温度比前一天高(10 -> 25)
2015-01-04 的温度比前一天高(20 -> 30)
#way1
select a.id from
weather as a join weather as b
on a.temperature > b.temperature
and dateDiff(a.recordDate,b.recordDate) = 1
#dateDiff返回两个参数之间间隔的天数
#way2
select b.id
from weather a join weather b
on b.recordDate = date_add(a.recordDate, interval 1 day)
and b.temperature > a.temperature
#way3
select b.id
from weather a,weather b
where a.recordDate = date_sub(b.recordDate,interval 1 day) and
a.Temperature < b.Temperature
# Write your MySQL query statement below
#下面是补充知识点:
#返回值是相差的天数
DATEDIFF('2007-12-31','2007-12-30');#1
DATEDIFF('2010-12-30','2010-12-31');#-1
#计算相差天数:
select TIMESTAMPDIFF(DAY,'2019-05-20', '2019-05-21'); # 1
#计算相差小时数:
select TIMESTAMPDIFF(HOUR, '2015-03-22 07:00:00', '2015-03-22 18:00:00'); # 11
#计算相差秒数:
select TIMESTAMPDIFF(SECOND, '2015-03-22 07:00:00', '2015-03-22 7:01:01'); # 61
#interval
"2015-01-03"+interval'1' day #2015-01-04
#从日期减去指定的时间间隔
DATE_SUB("2008-12-29",INTERVAL 2 DAY)
#从日期加上指定的时间间隔
adddate("2015-01-03",INTERVAL 1 day) #2015-01-04
#返回从0000到现在的天数
to_days("2015-01-04")