分析函数Over用法

1.
select empno, ename, deptno, sal,
       sum(sal) over (partition by deptno order by ename) x
from scott.emp;  
--注意PARTITION BY, ORDER BY

2.
select empno, ename, deptno, sal,
       sum(sal) over (partition by deptno order by ename
                      rows between unbounded preceding and current row) x
from scott.emp;  
--注意ROWS BETWEEN unbounded preceding AND current row  
--是指第一行至当前行的汇总

3.
select empno, ename, deptno, sal,
       sum(sal) over (partition by deptno order by ename
                      rows between current row and unbounded following) x
from scott.emp;  
--注意ROWS BETWEEN current row AND unbounded following  
--是指当前行到最后一行的汇总

4.
select empno, ename, deptno, sal,
       sum(sal) over (partition by deptno order by ename
                      rows between 1 preceding and current row) x
from scott.emp;  
--注意ROWS BETWEEN 1 preceding AND current row
--是指当前行的上一行(rownum-1)到当前行的汇总

5.
select empno, ename, deptno, sal,
       sum(sal) over (partition by deptno order by ename
                      rows between 1 preceding and 2 following) x
from scott.emp;  
--注意ROWS BETWEEN 1 preceding AND 1 following
--是指当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的汇总

6.
select empno, ename, deptno, sal,
       sum(sal) over (partition by deptno order by ename
    rows unbounded preceding) x from scott.emp;
---ROWS UNBOUNDED PRECEDING ---
--是指第一行至当前行的汇总;

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

转载于:http://blog.itpub.net/9182041/viewspace-676201/