[Oracle]高效的SQL語句之分析函式（一）（二）

fengzj發表於2008-11-12
OracleSQL函式

[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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章