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: http://www.itpub.net/324056.html
row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的).
与rownum的区别在于:使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序,而此函数在包含排序从句后是先排序再计算行号码.
借用上面的数据
select a,b,c,rownum from AA order by b
a b c rownum
1 1 1 1
1 2 2 2
2 2 5 4
1 3 3 3
3 4 6 5
row_number()和rownum差不多,功能更强一点(可以在各个分组内从1开时排序).
select a,b,c,row_number() OVER (PARTITION BY b order by a) from AA order by a
a b c rownum
1 1 1 1
1 2 2 1
1 3 3 1
2 2 5 2
3 4 6 1
select a,b,c,row_number() OVER (PARTITION BY b order by a) from AA order by b
a b c rownum
1 1 1 1
1 2 2 1
2 2 5 2
1 3 3 1
3 4 6 1
select a,b,c,row_number() OVER (PARTITION BY b order by a) from AA
a b c rownum
1 1 1 1
1 2 2 1
2 2 5 2
1 3 3 1
3 4 6 1