Oracle 取第一个、最后一个和上一个、下一个函数详解（first_value、last_value 和 lag、lead）

文章目录

• 1 概述
• 2 示例
• • 2.1 first_value() + last_value()
  • 2.2 lag() + lead()
• 3 扩展
• • 3.1 保持排序，且避免返回多行 keep()

1 概述

1. 说明 
   (1) 均属于 '分析函数': 每行一条记录（不聚合）
   (2) + over(partition by 列1 order by 列2) -- 一同使用
2. first_value() -- 第一个
   last_value()  -- 最后一个
   特别注意：是 '第一个' 到 '当前记录' 排行中的 '最后一个'
   rows between unbounded preceding and current row       
3. lag()  -- 上一个
   lead() -- 下一个

 
 

2 示例

2.1 first_value() + last_value()

with student_info as (
  select 1 sno, 'a' sname, 18 age, date '2020-01-01' create_date from dual union all
  select 2 sno, 'b' sname, 18 age, date '2020-02-01' create_date from dual union all
  select 3 sno, 'c' sname, 18 age, date '2020-03-01' create_date from dual
)
select si.sno,
       si.sname,
       si.age,
       si.create_date,
       first_value(si.sname) over(partition by si.age order by si.create_date) 第一条记录,
       last_value(si.sname) over(partition by si.age order by si.create_date) 至当前最后一条记录,
       first_value(si.sname) over(partition by si.age order by si.create_date desc) 所有记录中最后一条
  from student_info si;

 
 

查询结果：

sno		sname	age		create_date		第一条记录	至当前最后一条记录		所有记录中最后一条
1		a		18		2020/1/1		a			a					c
2		b		18		2020/2/1		a			b					c
3		c		18		2020/3/1		a			c					c

 
 

2.2 lag() + lead()

with student_info as (
  select 1 sno, 'a' sname, 18 age, date '2020-01-01' create_date from dual union all
  select 2 sno, 'b' sname, 18 age, date '2020-02-01' create_date from dual union all
  select 3 sno, 'c' sname, 18 age, date '2020-03-01' create_date from dual
)
select si.sno,
       si.sname,
       si.age,
       si.create_date,
       lag(si.sname) over(partition by si.age order by si.create_date) 上一条记录,
       lead(si.sname) over(partition by si.age order by si.create_date) 下一条记录
  from student_info si;

 
 

查询结果：若没有，则为空

sno		sname	age		create_date		上一条记录	下一条记录
1		a		18		2020/1/1					b
2		b		18		2020/2/1		a			c
3		c		18		2020/3/1		b

 
 

3 扩展

3.1 保持排序，且避免返回多行 keep()

/*
   要点：在子查询中 既能达到排序的目的，又不用担心 '返回多行'
   示例：查询各部门第一个入司的人
*/
select d.*,
       (select max(e.ename) keep(dense_rank first order by e.hiredate) 
               -- 报错：子查询返回多行
               -- max(e.ename) over(partition by e.deptno order by e.hiredate)
          from scott.emp e
         where e.deptno = d.deptno) 第一个入司的人
  from scott.dept d;