1.datediff()

datediff(startdate,enddate)

返回两个日期相差的天数

2.date_add()

#对于某个日期加上n分钟n秒

#对于某个日期加上n小时n分钟n秒

 #对于日期加上几天几小时几分钟几秒钟

3.lag()和lead()

• lag(字段名称 , 向上偏移量 , 超出范围时默认值) over (partion by …order by …)

• lead(字段名称 , 向下偏移量 , 超出范围时默认值) over (partion by …order by …)

 例题：leetcode 197题

 

 

#方法一
-- select id
-- from 
-- (
-- select id,recordDate,Temperature,lag(recordDate,1,Null) over (order by recordDate) yd,
-- lag(Temperature,1,100) over (order by recordDate) yt
-- from Weather
-- ) a
-- where datediff(a.recordDate,a.yd)=1
-- and a.Temperature>a.yt;

#方法二
-- select a.id
-- from Weather a
-- inner join Weather b
-- on datediff(a.recordDate,b.recordDate)=1
-- and a.Temperature>b.Temperature;

#方法三
select a.id 
    from weather a 
    inner join weather b 
    on (a.recorddate = adddate(b.recorddate,INTERVAL 1 day))
where a.temperature > b.temperature;