LAG & LEAD 获取上一行/下一行的数据
能来看这个帖子的,肯定都是有基础的,那就直接说重点了
LAG(字段,偏移量,默认值(可选))OVER(PARTITION BY 分组字段 ORDER BY 排序字段)
LEAD(字段,偏移量,默认值(可选))OVER(PARTITION BY 分组字段 ORDER BY 排序字段)
模拟需求
查询哪些学生第二次考试有进步
原数据
LAG & LEAD
如下,不多说
SELECT S.STUDENT_NAME,
S.SUBJECT,
S.TIMES,
S.SCORE,
LAG(S.SCORE, 1,0) OVER(PARTITION BY STUDENT_NAME, SUBJECT ORDER BY S.TIMES) LAST_SCORE,
LEAD(S.SCORE, 1,0) OVER(PARTITION BY STUDENT_NAME, SUBJECT ORDER BY S.TIMES) NEXT_SCORE
FROM STUDENT S
题外自关联方式
如下,不多说
SELECT *
FROM STUDENT A
LEFT JOIN STUDENT B
ON A.STUDENT_NAME = B.STUDENT_NAME
AND A.SUBJECT = B.SUBJECT
AND A.TIMES = B.TIMES + 1
WHERE 1 = 1
/*AND A.SCORE > B.SCORE*/
没了