lag/lead
lag与lead函数是跟偏移量相关的两个分析函数，通过这两个函数可以在一次查询中取出同一字段的前N行的数据(lag)和后N行的数据(lead)作为独立的列,从而更方便地进行进行数据过滤。这种操作可以代替表的自联接，并且LAG和LEAD有更高的效率。
LAG(EXP_STR,OFFSET,DEFVAL) OVER()
LEAD(EXP_STR,OFFSET,DEFVAL) OVER() 【EXP_STR】：待查询的参数列名
【OFFSET】：取偏移后的第几行数据
【DEFVAL】：没有符合条件时的默认值
create table student ( no varchar(20) not null primary key, name varchar(30), class varchar(2)); insert into student values(‘06117050‘, ‘张三‘, ‘01‘); insert into student values(‘06117051‘, ‘李四‘, ‘01‘); insert into student values(‘06117052‘, ‘王五‘, ‘01‘); insert into student values(‘06117053‘, ‘刘夕月‘, ‘01‘); insert into student values(‘06117054‘, ‘朱龙‘, ‘01‘); insert into student values(‘06117055‘, ‘小微‘, ‘01‘); insert into student values(‘06117056‘, ‘小红‘, ‘01‘); insert into student values(‘06117057‘, ‘张艳‘, ‘01‘); insert into student values(‘06127001‘, ‘钱多多‘, ‘02‘); insert into student values(‘06127002‘, ‘张杰‘, ‘02‘); insert into student values(‘06127003‘, ‘王玲‘, ‘02‘); insert into student values(‘06127004‘, ‘华少‘, ‘02‘); commit;
select s.no "学号", lead(s.no, 1) over(partition by s.class order by s.no) "下一个同学的学号", s.name "姓名", s.class "班级" from student s
结果
--获取下一个学号 select s.no "学号", lag(s.no, 1) over(partition by s.class order by s.no) "上一个同学的学号", s.name "姓名", s.class "班级" from student s
结果：
原文：https://www.cnblogs.com/zhuhaiying/p/12228154.html
