【SQL查询】获取同一字段前/后n行的值_lag/lead

lag/lead

1. 简介：

lag与lead函数是跟偏移量相关的两个分析函数，通过这两个函数可以在一次查询中取出同一字段的前N行的数据(lag)和后N行的数据(lead)作为独立的列,从而更方便地进行进行数据过滤。这种操作可以代替表的自联接，并且LAG和LEAD有更高的效率。

2. 语法

 LAG(EXP_STR,OFFSET,DEFVAL) OVER()  LEAD(EXP_STR,OFFSET,DEFVAL) OVER() 

3. 说明

【EXP_STR】：待查询的参数列名
【OFFSET】：取偏移后的第几行数据
【DEFVAL】：没有符合条件时的默认值

4. 示例

• 　　初始化数据：

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;

View Code

• 　　获取同一班级中，下一位同学的学号：

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

　　结果：