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> (编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|