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中该函数用法不同
上一篇: 设计模式系列 - 观察者模式
下一篇: 曲折的建站之旅