欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

RisingTemperature:一道关于sql语言的面试题目

程序员文章站 2022-05-02 11:57:37
第一道关于sql语言的题目 given aweathertable, write a sql query to find all dates' ids with higher tempera...

第一道关于sql语言的题目

given aweathertable, 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 aboveweathertable:

+----+
| id |
+----+
|  2 |
|  4 |
+----+

解答:

因为太久没写过sql了,解答方式是看了fabrizio3的答案

select wt1.id 
from weather wt1, weather wt2
where wt1.temperature > wt2.temperature and 
      to_days(wt1.date)-to_days(wt2.date)=1;//to_days把日期转换为从0到该日期的天数,从而判断w1是否w2的后一天

hsldymq的答案(mysql)
select a.id from weather as a, weather as b
where datediff(a.date, b.date)=1 and a.temperature > b.temperature//唯一区别是用了datediff方法,该方法在oracle中没有,在ms sql server中该函数用法不同