ORACLE 在8i的时候开始支持over函数,在10g之前的over函数必须有参数。
1、求和
假设现在要查询t_emp表中的ename,sal,sal总和:
SELECT T.ENAME, T.SAL, SUM(T.SAL) OVER() AS SAL_TOTAL FROM T_EMP T;
2、求所占比例
假设现在要查询t_emp表中的ename,sal,sal总和,sal占总和比例(保留俩位小数):
SELECT T.ENAME, T.SAL, SUM(T.SAL) OVER() AS SAL_TOTAL, ROUND(T.SAL/(SUM(T.SAL) OVER()), 2) AS SAL_PERCENT FROM T_EMP T;
3、连续求和
假设现在要查询t_emp表中的ename,sal,sal总和,按ename排序的总和:
SELECT T.ENAME, T.SAL, SUM(T.SAL) OVER() AS SAL_TOTAL, SUM(T.SAL) OVER(ORDER BY T.ENAME) AS SAL_TOTAL2 FROM T_EMP T;
4、分部门连续求和
SELECT ENAME, DEPTNO, SAL, SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME) AS SAL_TOTAL FROM T_EMP;
5、得到当前行、上一行、下一行的数据
假如现在要查询t_emp表中ename,current_sal,previous_sal,next_sal:(按ename排序)
SELECT ENAME, SAL CURRENT_SAL, LAG(SAL) OVER(ORDER BY ENAME) AS PREVIOUS_SAL, LEAD(SAL) OVER(ORDER BY ENAME) AS NEXT_SAL FROM T_EMP;
注意:lag()和lead()俩分析函数要和over()函数一起使用。