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

第一道关于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中该函数用法不同