【sql dateDiff】197. Rising Temperature

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

+---------+------------------+------------------+
| Id(INT) | RecordDate(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:

+----+
| Id |
+----+
|  2 |
|  4 |
+----+

select a.Id as Id from Weather as a left join Weather as b on DATEDIFF(a.RecordDate,b.RecordDate) = 1 where a.Temperature>b.Temperature;

  

DATEDIFF(datepart,startdate,enddate)

第一个参数为没有的时候,默认返回两个时间之间的天数

SELECT DATEDIFF(day,'2008-06-05','2008-08-05') AS DiffDate

 



posted on 2018-10-12 19:11  baiyuhong  阅读(183)  评论(0编辑  收藏  举报