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 ---
--是指第一行至当前行的汇总;
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/