1)lead的窗口函数用法 SELECT id,score,Lead(score,2) over(order by id) lead_score,-- score数列向前推动2位,后面就腾空了2个位置 Lag(score,2) over(order by id) lag_score, -- score数列向后推2位,腾空2个位置 lag(score,2,666) over(order by id) lag_score_3 -- score数列向后推动2位,空值被填充为666 FROM exam_record; 3)datediff和timestampdiff函数的区别 直接说可能有点懵,看完差别后,具体来看下面这个例子: #先看第一组时间差是23小时,日期差(9月5日-9月4日)是1天 select DATEDIFF('2021-09-05 12:00:00','2021-09-04 11:00:00') datediff_1, TIMESTAMPDIFF(hour,'2021-09-04 12:00:00','2021-09-05 11:00:00') hourdiff_1, TIMESTAMPDIFF(day,'2021-09-04 12:00:00','2021-09-05 11:00:00')timediff_1; #再看第二组时间差是28小时,日期差(9月5日-9月4日)是1天 select DATEDIFF('2021-09-05 16:00:00','2021-09-04 11:00:00') datediff_2, TIMESTAMPDIFF(hour,'2021-09-04 12:00:00','2021-09-05 16:00:00') hourdiff_2, TIMESTAMPDIFF(day,'2021-09-04 12:00:00','2021-09-05 16:00:00')timediff_2;
2)datediff 函数