ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [Oracle]高效的SQL语句之分析函数(一)(二)

[Oracle]高效的SQL语句之分析函数(一)(二)

原创 Linux操作系统 作者:fengzj 时间:2008-11-12 16:39:47 0 删除 编辑

[Oracle]高效的SQL语句之分析函数(一)--sum()

 

实际应用中我们可以通过sum()统计出组中的总计或者是累加值,具体示例如下:

1.创建演示表

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);


2. sum()语句如下:

select deptno,
       ename,
       sal,
  
--按照部门薪水累加(order by改变了分析函数的作用,只工作在当前行和前一行,而不是所有行)

       sum(sal) over (partition by deptno order by sal) CumDeptTot,  
       
sum(sal) over (partition by deptno) SalByDept,  --统计一个部门的薪水

       sum(sal) over (order by deptno,sal) CumTot,  --所有雇员的薪水一行一行的累加
       sum(sal) over () TotSal  --统计总薪水
  from emp
 
order by
 deptno, sal

3. 结果如下:

10    MILLER    1300.00    1300    8750    1300    29025
10    CLARK    2450.00    3750    8750    3750    29025
10    KING    5000.00    8750    8750    8750    29025
20    SMITH    800.00    800    10875    9550    29025
20    ADAMS    1100.00    1900    10875    10650    29025
20    JONES    2975.00    4875    10875    13625    29025
20    SCOTT    3000.00    10875    10875    19625    29025
20    FORD    3000.00    10875    10875    19625    29025
30    JAMES    950.00    950    9400    20575    29025
30    WARD    1250.00    3450    9400    23075    29025
30    MARTIN    1250.00    3450    9400    23075    29025
30    TURNER    1500.00    4950    9400    24575    29025
30    ALLEN    1600.00    6550    9400    26175    29025
30    BLAKE    2850.00    9400    9400    29025    29025

 

[Oracle]高效的SQL语句之分析函数(二)--max()

如果我们按照示例想得到每个部门薪水值最高的雇员的纪录,可以有四种方法实现:

先创建示例表

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);


方法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


方法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 

结果如下:

10    5000.00    7839
20    3000.00    7788
20    3000.00    7902
30    2850.00    7698

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/219982/viewspace-492459/,如需转载,请注明出处,否则将追究法律责任。

上一篇: ORACLE函数大全
请登录后发表评论 登录
全部评论

注册时间:2008-11-11

  • 博文量
    76
  • 访问量
    177175