over(partition by cloumn_name)语法

create table test
(
   a  number,
   b  number,
   c  number
);
insert into test (A, B, C) values (1, 1, 1);
insert into test (A, B, C) values (1, 2, 2);
insert into test (A, B, C) values (1, 3, 3);
insert into test (A, B, C) values (2, 2, 5);
insert into test (A, B, C) values (3, 4, 6);
insert into test (A, B, C) values (1, 2, 77);
commit;

 

SQL> select * from test;

         A          B          C
---------- ---------- ----------
         1          1          1
         1          2          2
         1          3          3
         2          2          5
         3          4          6
         1          2         77

已选择6行。

SQL> 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         84
         1          2         77        84
         2          2          5         84
         1          3          3          3
         3          4          6          6

已选择6行。

SQL> select a,b,c, SUM(C)  OVER (PARTITION BY null) C_Sum from test ;

         A          B          C      C_SUM
---------- ---------- ---------- ----------
         1          1          1         94
         1          2          2         94
         1          2         77        94
         2          2          5         94
         3          4          6         94
         1          3          3         94

已选择6行。