
SELECT E.DEPTNO,
E.EMPNO,
E.ENAME,
E.SAL,
MAX(E.SAL)
OVER(PARTITION
BY E.DEPTNO
/*ORDER BY E.SAL*/) MAX_SAL
FROM EMP E;

示例2:对各部门进行分组,并附带显示第一行至当前行的汇总

SELECT EMPNO,
ENAME,
DEPTNO,
SAL,
--注意ROWS BETWEEN unbounded preceding AND current row 是指第一行至当前行的汇总
SUM(SAL)
OVER(PARTITION
BY DEPTNO
ORDER BY ENAME
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) max_sal
FROM SCOTT.EMP;

结果如下:

示例3:当前行至最后一行的汇总

SELECT EMPNO,
ENAME,
DEPTNO,
SAL,
--注意ROWS BETWEEN current row AND unbounded following 指当前行到最后一行的汇总
SUM(SAL)
OVER(PARTITION
BY DEPTNO
ORDER BY ENAME
ROWS BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING) max_sal
FROM SCOTT.EMP;

结果如下:

示例4:当前行的上一行(rownum-1)到当前行的汇总

SELECT EMPNO,
ENAME,
DEPTNO,
SAL,
--注意ROWS BETWEEN 1 preceding AND current row 是指当前行的上一行(rownum-1)到当前行的汇总
SUM(SAL)
OVER(PARTITION
BY DEPTNO
ORDER BY ENAME ROWS
BETWEEN 1 PRECEDING
AND CURRENT ROW) max_sal
FROM SCOTT.EMP;

结果如下:

示例5:当前行的上一行(rownum-1)到当前行的下两行(rownum+2)的汇总

SELECT EMPNO,
ENAME,
DEPTNO,
SAL,
--注意ROWS BETWEEN 1 preceding AND 1 following 是指当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的汇总
SUM(SAL)
OVER(PARTITION
BY DEPTNO
ORDER BY ENAME
ROWS BETWEEN 1 PRECEDING
AND 2 FOLLOWING) max_sal
FROM SCOTT.EMP;

结果如下:

回到顶部
二、其他
1、NULLS FIRST/LAST 将空值字段记录放到最前或最后显示
说明:
通过RANK()、DENSE_RANK()、ROW_NUMBER()对记录进行全排列、分组排列取值,但有时候,会遇到空值的情况,空值会影响得到的结果的正确性
NULLS FIRST/LAST 可以帮助我们在处理含有空值的排序排列中,将空值字段记录放到最前或最后显示,帮助我们得到期望的结果。
SELECT E.ENAME,
E.JOB,
E.SAL,
E.DEPTNO,
RANK() OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL NULLS LAST)
FROM EMP E;
结果如下:

2、NTILE(n)
示例: