Oracle keep() 详解

文章目录

• 1 概述
• 2 示例

1 概述

1. kee() 和 一般分析函数 的区别
   (1) 聚合函数 + over(): 一行一条记录 -- 一般分析函数
   (2) 聚合函数 + keep() + over(): '分组'

2. 语法格式
   max|min(列1) keep(dense_rank first|last order by 列2) -- 必选
   over(partition by 列3) -- 可选
   -- dense_rank first 取第一条，last 取最后一条
           

2 示例

/*
   示例：查询各部门第一个入司的人
   要点：在子查询中 既能达到排序的目的，又不用担心 '返回多行'
*/
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;
           

以下同理：

with dept as (
  select 1 dept_no, '部门1' dept_name from dual union all
  select 2 dept_no, '部门2' dept_name from dual
), emp as (
  select 1 emp_no, 'a' emp_name, 1 dept_no, date '2020-01-01' hire_date from dual union all
  select 2 emp_no, 'b' emp_name, 1 dept_no, date '2020-02-01' hire_date from dual union all
  select 3 emp_no, 'c' emp_name, 2 dept_no, date '2020-03-01' hire_date from dual
)

select d.*,
       (select max(e.emp_name) keep(dense_rank first order by e.hire_date) 
          from emp e
         where e.dept_no = d.dept_no) emp_name
  from dept d;
           

查询结果：

dept_no		dept_name		emp_name
1			部门1			a
2			部门2			c