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