Last login: Sun Jul 29 15:29:04 2007 from 192.168.39.222
[root@localhost ~]# su - oracle
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Sep 12 22:29:45 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn scott/tiger
Connected.
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
--下面这个sql实际上是先根据deptno，ename排序后再将sal列的值不断求和(求和的行数为当前行和其之前的行)
SQL> select sum(sal) over(order by deptno,ename),deptno,ename,sal from emp;
SUM(SAL)OVER(ORDERBYDEPTNO,ENAME) DEPTNO ENAME SAL
--------------------------------- ---------- ---------- ----------
2450 10 CLARK 2450
7450 10 KING 5000
8750 10 MILLER 1300
9850 20 ADAMS 1100
12850 20 FORD 3000
15825 20 JONES 2975
18825 20 SCOTT 3000
19625 20 SMITH 800
21225 30 ALLEN 1600
24075 30 BLAKE 2850
25025 30 JAMES 950
SUM(SAL)OVER(ORDERBYDEPTNO,ENAME) DEPTNO ENAME SAL
--------------------------------- ---------- ---------- ----------
26275 30 MARTIN 1250
27775 30 TURNER 1500
29025 30 WARD 1250
14 rows selected.
--下面这个sql实际上是先将所有的数据按照deptno分组，然后对组内的数据进行按ename进行排序，
--然后依次求和(依然是求当前行跟之前的行的sal值之和)
SQL> select sum(sal) over(partition by deptno order by ename),deptno,ename,sal from emp;
SUM(SAL)OVER(PARTITIONBYDEPTNOORDERBYENAME) DEPTNO ENAME SAL
------------------------------------------- ---------- ---------- ----------
2450 10 CLARK 2450
7450 10 KING 5000
8750 10 MILLER 1300
1100 20 ADAMS 1100
4100 20 FORD 3000
7075 20 JONES 2975
10075 20 SCOTT 3000
10875 20 SMITH 800
1600 30 ALLEN 1600
4450 30 BLAKE 2850
5400 30 JAMES 950
SUM(SAL)OVER(PARTITIONBYDEPTNOORDERBYENAME) DEPTNO ENAME SAL
------------------------------------------- ---------- ---------- ----------
6650 30 MARTIN 1250
8150 30 TURNER 1500
9400 30 WARD 1250
14 rows selected.
--同上，只是这里改变了ename的排序方式，默认是升序，这里改为降序
SQL> select sum(sal) over(partition by deptno order by ename desc),deptno,ename,sal from emp;
SUM(SAL)OVER(PARTITIONBYDEPTNOORDERBYENAMEDESC) DEPTNO ENAME SAL
----------------------------------------------- ---------- ---------- ----------
1300 10 MILLER 1300
6300 10 KING 5000
8750 10 CLARK 2450
800 20 SMITH 800
3800 20 SCOTT 3000
6775 20 JONES 2975
9775 20 FORD 3000
10875 20 ADAMS 1100
1250 30 WARD 1250
2750 30 TURNER 1500
4000 30 MARTIN 1250
SUM(SAL)OVER(PARTITIONBYDEPTNOORDERBYENAMEDESC) DEPTNO ENAME SAL
----------------------------------------------- ---------- ---------- ----------
4950 30 JAMES 950
7800 30 BLAKE 2850
9400 30 ALLEN 1600
14 rows selected.
--当over里面不带任何参数的时候，就是将所有的sal求和
SQL> select sum(sal) over(),deptno,ename,sal from emp;
SUM(SAL)OVER() DEPTNO ENAME SAL
-------------- ---------- ---------- ----------
29025 20 SMITH 800
29025 30 ALLEN 1600
29025 30 WARD 1250
29025 20 JONES 2975
29025 30 MARTIN 1250
29025 30 BLAKE 2850
29025 10 CLARK 2450
29025 20 SCOTT 3000
29025 10 KING 5000
29025 30 TURNER 1500
29025 20 ADAMS 1100
SUM(SAL)OVER() DEPTNO ENAME SAL
-------------- ---------- ---------- ----------
29025 30 JAMES 950
29025 20 FORD 3000
29025 10 MILLER 1300
14 rows selected.
--带一个partition by的时候就是将所有的数据分组，然后再求和，但是不是依次求和，而是一次性求和
SQL> select sum(sal) over(partition by deptno),deptno,ename,sal from emp;
SUM(SAL)OVER(PARTITIONBYDEPTNO) DEPTNO ENAME SAL
------------------------------- ---------- ---------- ----------
8750 10 CLARK 2450
8750 10 KING 5000
8750 10 MILLER 1300
10875 20 JONES 2975
10875 20 FORD 3000
10875 20 ADAMS 1100
10875 20 SMITH 800
10875 20 SCOTT 3000
9400 30 WARD 1250
9400 30 TURNER 1500
9400 30 ALLEN 1600
SUM(SAL)OVER(PARTITIONBYDEPTNO) DEPTNO ENAME SAL
------------------------------- ---------- ---------- ----------
9400 30 JAMES 950
9400 30 BLAKE 2850
9400 30 MARTIN 1250
14 rows selected.
