oracle over usage!-凯发app官方网站

oracle 语法之 over (partition by ..)

select * from test

数据:
a   b   c
1   1   1
1   2 2
1   3   3
2   2 5
3   4 6

---将b栏位值相同的对应的c 栏位值加总
select a,b,c, sum(c) over (partition by b) c_sum
from test

a   b   c   c_sum
1 1 1 1
1 2 2 7
2 2 5 7
1 3 3 3
3 4 6 6

---如果不需要已某个栏位的值分割,那就要用 null

eg: 就是将c的栏位值summary 放在每行后面

select a,b,c, sum(c) over (partition by null) c_sum
from test

a   b   c   c_sum
1 1 1 17
1 2 2 17
1 3 3 17
2 2 5 17
3 4 6 17

this part from:
----------------
gan's practise:

sql> select deptno,ename,job,sal from emp order by deptno;

    deptno ename      job              sal
---------- ---------- --------- ----------
        10 clark      manager         2450
        10 king       president       5000
        10 miller     clerk           1300
        20 jones      manager         2975
        20 ford       analyst         3000
        20 adams      clerk           1100
        20 smith      clerk            800
        20 scott      analyst         3000
        30 ward       salesman        1250
        30 turner     salesman        1500
        30 allen      salesman        1600
        30 james      clerk            950
        30 blake      manager         2850
        30 martin     salesman        1250

14 rows selected.
sql> @select_over.sql

    deptno ename      job              sal    sal_pet
---------- ---------- --------- ---------- ----------
        10 clark      manager         2450         28
        10 king       president       5000 57.1428571
        10 miller     clerk           1300 14.8571429
        20 jones      manager         2975 27.3563218
        20 ford       analyst         3000 27.5862069
        20 adams      clerk           1100 10.1149425
        20 smith      clerk            800 7.35632184
        20 scott      analyst         3000 27.5862069
        30 ward       salesman        1250 13.2978723
        30 turner     salesman        1500 15.9574468
        30 allen      salesman        1600 17.0212766
        30 james      clerk            950 10.106383
        30 blake      manager         2850 30.3191489
        30 martin     salesman        1250 13.2978723

14 rows selected.
sql> l
1 select deptno, ename, job, sal,
2         (sal*100)/sum(sal) over (partition by deptno) sal_pet
3* from emp

阅读(1810) | 评论(0) | 转发(0) |

上一篇：oracle select subquery (with)

下一篇：oci 指示器变量!

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们