oracle 求每组的最小值的人员信息

1.求每组的最小值的人员信息
–普通写法 这种要访问2次emp
with tt as (
select e.deptno,min(e.sal) min_sal from emp e group by e.deptno
)
select e.deptno,e.ename,e.sal from emp e,tt
where e.deptno=tt.deptno
and e.sal=tt.min_sal ;

–分析函数 ,这种只需要访问一次emp(推荐,效率更高)
–方式1
select * from (
select e.empno,e.deptno,e.sal,
dense_rank() over(partition by e.deptno order by sal) rn
from emp e) where rn=1 ;–and tt.deptno is not null;

方式2:感觉做个执行计划稍微好点
select * from (
select e.ename,e.sal,e.deptno,max(sal) over(partition by e.deptno) maxsal from emp e) tt
where tt.maxsal=tt.sal;

–方式3 keep
select * from (
select e.deptno,
e.sal,
e.ename,
max(e.sal) keep(dense_rank first order by null) over(partition by e.deptno) maxsal
from emp e)tt
where tt.sal=tt.maxsal ;

2.去重deptno
delete from emp e1
where rowid <(
select max(rowid) from emp e2 where e1.deptno= e2.deptno
);
这种访问3次emp

–分析函数 关键是筛选rowid
delete from emp e1 where e1.rowid not in(
select rowid rod from (
select rowid,
row_number() over(partition by e2.deptno order by null) rn
from emp e2 ) where rn=1
) ;
这种访问2次emp