分析函数计算基于组的某种聚合值

最新推荐文章于 2024-03-29 14:43:49 发布

hooyang

最新推荐文章于 2024-03-29 14:43:49 发布

阅读量585

点赞数

分类专栏： oracle学习笔记

本文链接：https://blog.csdn.net/hooyang/article/details/517474

版权

oracle学习笔记专栏收录该内容

8 篇文章 0 订阅

订阅专栏

select ename, deptno, sal,
       sum(sal) over
         (order by deptno, ename) running_total,
       sum(sal) over
         (partition by deptno
          order by ename) department_total,
       row_number() over
         (partition by deptno
          order by ename) seq
   from scott.emp
   order by deptno, ename

ENAME          DEPTNO        SAL RUNNING_TOTAL DEPARTMENT_TOTAL        SEQ
---------- ---------- ---------- ------------- ---------------- ----------
CLARK              10       2450          2450             2450          1
KING                        5000          7450             7450          2
MILLER                      1300          8750             8750          3

ADAMS              20       1100          9850             1100          1
FORD                        3000         12850             4100          2
JONES                       2975         15825             7075          3
SCOTT                       3000         18825            10075          4
SMITH                        800         19625            10875          5

ALLEN              30       1600         21225             1600          1
BLAKE                       2850         24075             4450          2
JAMES                        950         25025             5400          3
MARTIN                      1250         26275             6650          4
TURNER                      1500         27775             8150          5
WARD                        1250         29025             9400          6

14 rows selected.