首页 > itarticle > leetcode 197.rising temperature

leetcode 197.rising temperature

admin 11月 11, 2020 0

问题描述：

Given a Weather table, write a SQL query to find all dates’ Ids with higher temperature compared to its previous (yesterday’s) dates.

1
2
3
4
5
6
7
8
 
+---------+------------+------------------+
| Id(INT) | Date(DATE) | Temperature(INT) |
+---------+------------+------------------+
|       1 | 2015-01-01 |               10 |
|       2 | 2015-01-02 |               25 |
|       3 | 2015-01-03 |               20 |
|       4 | 2015-01-04 |               30 |
+---------+------------+------------------+

For example, return the following Ids for the above Weather table:

1
2
3
4
5
6
 
+----+
| Id |
+----+
|  2 |
|  4 |
+----+

问题求解：

可以对 Weather 表相邻两天的记录进行连接，筛选出前一天气温低于后一天的记录

1
2
3
4
 
SELECT b.Id FROM Weather AS a 
join Weather AS b 
ON TO_DAYS(a.DATE)=TO_DAYS(b.DATE)-1 
WHERE a.Temperature<b.Temperature;

可以在连接时加以限定：

1
2
3
4
 
SELECT b.Id FROM Weather a 
join Weather b 
ON TO_DAYS(a.DATE)=TO_DAYS(b.DATE)-1 
AND a.Temperature<b.Temperature;

也可以不连接直接进行多表查询：

1
2
3
 
SELECT b.Id FROM Weather a ,Weather b 
WHERE TO_DAYS(a.DATE)=TO_DAYS(b.DATE)-1 
AND a.Temperature<b.Temperature;
微海报分享

近期文章

近期评论

标签

热门

文章归档

分类目录

功能