一张Weather表,写出一个SQL语句实现昨天比今天气温高的Id
+---------+------------+------------------+ | 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 | +---------+------------+------------------+结果:
+----+ | Id | +----+ | 2 | | 4 | +----+SQL:
MySQL
select id from Weather as t1,Weather as t2 where datediff(day,t1.Date,t2.Date)=1 and t1.Temperature<t2.Temperature
或者
select id from Weather as t1 inner join Weather as t2 on TO_DAYSt1.Date)+1=TO_DAYS(t2.Date) and t1.Temperature<t2.Temperature
DATEDIFF() 函数返回两个日期之间的天数。
DATEDIFF(datepart,startdate,enddate)
startdate 和 enddate 参数是合法的日期表达式。
TO_DAYS(date)
给出一个日期 date
,返回一个天数(从 0 年开始的天数)