select ename,sal,sum(sal) over (partition by ename order by sal,empno) as running_total
from emp1
order by 2
按ename 汇总sal
over()开窗函数和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。
SQL> select e.empno,e.ename,e.job,e.sal,e.deptno, sum(e.sal) over (partition by e.deptno) as total_sal
2 from emp e;
EMPNO ENAME JOB SAL DEPTNO TOTAL_SAL
---------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 2450 10 8750
7839 KING PRESIDENT 5000 10 8750
7934 MILLER CLERK 1300 10 8750
7566 JONES MANAGER 2975 20 10875
7902 FORD ANALYST 3000 20 10875
7876 ADAMS CLERK 1100 20 10875
7369 SMITH CLERK 800 20 10875
7788 SCOTT ANALYST 3000 20 10875
7521 WARD SALESMAN 1250 30 9400
7844 TURNER SALESMAN 1500 30 9400
7499 ALLEN SALESMAN 1600 30 9400
EMPNO ENAME JOB SAL DEPTNO TOTAL_SAL
---------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 950 30 9400
7698 BLAKE MANAGER 2850 30 9400
7654 MARTIN SALESMAN 1250 30 9400
已选择14行。
聚合函数:
SQL> select sum(sal) ,deptno from emp group by deptno;
SUM(SAL) DEPTNO
---------- ----------
9400 30
10875 20
8750 10
SQL>
