本系列文章导航
[Oracle]高效的SQL语句之分析函数(一)--sum()
[Oracle]高效的SQL语句之分析函数(二)--max()
[Oracle]高效的SQL语句之分析函数(三)--row_number() /rank()/dense_rank()
[Oracle]高效的SQL语句之分析函数(四)--lag()/lead()
如果我们按照示例想得到每个部门薪水值最高的雇员的纪录,可以有四种方法实现:
先创建示例表
create
table
emp
as
select * from scott.emp;
alter table emp
add constraint emp_pk
primary key (empno);
create table dept
as
select * from scott.dept;
alter table dept
add constraint dept_pk
primary key (deptno);
as
select * from scott.emp;
alter table emp
add constraint emp_pk
primary key (empno);
create table dept
as
select * from scott.dept;
alter table dept
add constraint dept_pk
primary key (deptno);
方法1.emp中的每一行都会进行max比较,费时
select
*
from
emp emp1
where
emp1.sal
=
(
select
max
(emp2.sal)
from
emp emp2
where
emp2.deptno
=
emp1.deptno)
方法2.先子查询查找出max sal,然后与emp表相关联,如果逻辑复杂会产生较多代码
select
*
from
emp emp1,(
select
deptno,
max
(sal) maxsal
from
emp emp2
group
by
emp2.deptno) emp3
where
emp1.deptno
=
emp3.deptno
and
emp1.sal
=
emp3.maxsal
方法3.使用max分析函数
select
deptno,maxsal,empno
from
(
select max (sal) over (partition by deptno) maxsal,emp. * from emp) emp2
where emp2.sal = emp2.maxsal
select max (sal) over (partition by deptno) maxsal,emp. * from emp) emp2
where emp2.sal = emp2.maxsal
方法4.使用dense_rank分析函数,如果一个部门可能存在多笔最大薪水,就不能使用row_number()分析函数
select
deptno,sal,empno
from
(
select emp. * ,DENSE_RANK() over (partition by deptno order by sal desc ) rownumber from emp) emp2
where rownumber = 1
select emp. * ,DENSE_RANK() over (partition by deptno order by sal desc ) rownumber from emp) emp2
where rownumber = 1
结果如下:
10
5000.00
7839
20 3000.00 7788
20 3000.00 7902
30 2850.00 7698
20 3000.00 7788
20 3000.00 7902
30 2850.00 7698
博文来源:http://blog.csdn.net/huanghui22/archive/2007/05/03/1595161.aspx