(1)oracle使用keep分析函数取最值记录
-- 取工资sal最大的雇员姓名及其工资，以及工资sal最少的雇员姓名及其工资
select
deptno,
empno,
ename,
sal,
max(ename) keep(dense_rank FIRST order by sal) over (partition by deptno) as min_sal_man,
max(sal) keep(dense_rank FIRST order by sal) over (partition by deptno) as min_sal,
max(ename) keep(dense_rank LAST order by sal) over (partition by deptno) as max_sal_man,
max(sal) keep(dense_rank LAST order by sal) over (partition by deptno) as max_sal
from emp
where deptno=10
结果如下：
从语句中可以看到，ename和sal都是用的max()，这样做的目的是为了去除由于keep()函数得到的有重复值的数据结果集。这样用有一个弊端，加入部门20有两个相同的最大SAL的人，部门30有两个相同的最小SAL的人，如果按照这种方法取出来的数据，就不一定准确了，重复的人会被去除掉。
我们用下面的语句来修改一下：
select
deptno,
empno,
ename,
sal,
max(ename) keep(dense_rank FIRST order by sal) over (partition by deptno) as min_sal_man,
max(sal) keep(dense_rank FIRST order by sal) over (partition by deptno) as min_sal,
max(ename) keep(dense_rank LAST order by sal) over (partition by deptno) as max_sal_man,
max(sal) keep(dense_rank LAST order by sal) over (partition by deptno) as max_sal,
<
