取一个部门的最高工资和最低工资
select deptno,empno,max(ename) keep(dense_rank first order by sal ) over (partition by deptno) as 工资最低的人,
max(ename) keep(dense_rank last order by sal) over(partition by deptno) as 工资最高的人,
ename,sal
from emp
order by 1,3

2 另外一种写法：

select deptno,empno,first_value(ename) over(partition by deptno order by sal desc) as 最高工资的人,
ename,sal from emp
where deptno=10
order by 1,5 desc;

ITPub博客

分析函数keep over(dense_rank frist) over 取最高工资和最低工资

最新文章