环境：windows 2000 server + Oracle8.1.7 + sql*plus 目的：以oracle自带的scott模式为测试环境，主要通过试验体会分析函数的用法。 类似 sum(...) over ... 的使用
1.原表信息： SQL> break on deptno skip 1; -- 为效果更明显，把不同部门的数据隔段显示。
SQL> select deptno,ename,sal
2; from emp
3; order by deptno; ;DEPTNO ENAME;;SAL
---------- ---------- ----------
10 CLARK;2450
KING;;5000
MILLER1300 ;;;;;20 SMITH;;800
ADAMS;1100
FORD;;3000
SCOTT;3000
JONES;2975 ;;;;;30 ALLEN;1600
BLAKE;2850
MARTIN1250
JAMES;;950
TURNER1500
WARD;;1250
已选择14行。 ; 2.先来一个简单的，注重over(...)条件的不同，
使用 sum(sal) over (order by ename)... 查询员工的薪水“连续”求和,
注重over (order; by ename)假如没有order by 子句，求和就不是“连续”的，
放在一起，体会一下不同之处： SQL> break on '' -- 取消数据分段显示
SQL> select deptno,ename,sal,
2; sum(sal) over (order by ename) 连续求和,
3; sum(sal) over () 总和,; -- 此处sum(sal) over () 等同于sum(sal)

; 4; 100*round(sal/sum(sal) over (),4) "份额(%)"
5; from emp
6; / ;DEPTNO ENAME;;SAL连续求和;;;;总和;份额(%)
---------- ---------- ---------- ---------- ---------- ----------
20 ADAMS;1100;;;;1100;;;29025;;;;3.79
30 ALLEN;1600;;;;2700;;;29025;;;;5.51
30 BLAKE;2850;;;;5550;;;29025;;;;9.82
10 CLARK;2450;;;;8000;;;29025;;;;8.44
20 FORD;;3000;;;11000;;;29025;;;10.34
30 JAMES;;950;;;11950;;;29025;;;;3.27
20 JONES;2975;;;14925;;;29025;;;10.25
10 KING;;5000;;;19925;;;29025;;;17.23
30 MARTIN1250;;;21175;;;29025;;;;4.31
10 MILLER1300;;;22475;;;29025;;;;4.48
20 SCOTT;3000;;;25475;;;29025;;;10.34

;;;;;20 SMITH;;800;;;26275;;;29025;;;;2.76
30 TURNER1500;;;27775;;;29025;;;;5.17
30 WARD;;1250;;;29025;;;29025;;;;4.31 已选择14行。
3.使用子分区查出各部门薪水连续的总和。注重按部门分区。注重over(...)条件的不同，
sum(sal) over (partition by deptno order by ename) 按部门“连续”求总和
sum(sal) over (partition by deptno) 按部门求总和
sum(sal) over (order by deptno，ename) 不按部门“连续”求总和
sum(sal) over () 不按部门，求所有员工总和，效果等同于sum(sal)。 SQL> break on deptno skip 1; -- 为效果更明显，把不同部门的数据隔段显示。
SQL> select deptno,ename,sal,
2; sum(sal) over (partition by deptno order by ename) 部门连续求和,--各部门的薪水"连续"求和
3; sum(sal) over (partition by deptno) 部门总和,; -- 部门统计的总和，同一部门总和不变
4; 100*round(sal/sum(sal) over (partition by deptno),4) "部门份额(%)",
5; sum(sal) over (order by deptno,ename) 连续求和, --所有部门的薪水"连续"求和
6; sum(sal) over () 总和,; -- 此处sum(sal) over () 等同于sum(sal)，所有员工的薪水总和
7; 100*round(sal/sum(sal) over (),4) "总份额(%)"
8; from emp
9; / DEPTNO ENAME;SAL 部门连续求和部门总和 部门份额(%)连续求和总和; 总份额(%)
------ ------ ----- ------------ ---------- ----------- ---------- ------ ----------
10 CLARK2450 2450;;;;8750; 28;;;;2450; 29025;;;;8.44
KING;5000 7450;;;;8750;;;;57.14;;;;7450; 29025;;;17.23
MILLER; 1300 8750;;;;8750;;;;14.86;;;;8750; 29025;;;;4.48 ;20 ADAMS1100 1100;;;10875;;;;10.11;;;;9850; 29025;;;;3.79

;;;;FORD;3000 4100;;;10875;;;;27.59;;;12850; 29025;;;10.34
JONES2975 7075;;;10875;;;;27.36;;;15825; 29025;;;10.25
SCOTT3000;;;;;10075;;;10875;;;;27.59;;;18825; 29025;;;10.34
SMITH;800;;;;;10875;;;10875;;;;;7.36;;;19625; 29025;;;;2.76 ;30 ALLEN1600 1600;;;;9400;;;;17.02;;;21225; 29025;;;;5.51
BLAKE2850 4450;;;;9400;;;;30.32;;;24075; 29025;;;;9.82
JAMES;950 5400;;;;9400;;;;10.11;;;25025; 29025;;;;3.27
MARTIN; 1250 6650;;;;9400;;;;;13.3;;;26275; 29025;;;;4.31
TURNER; 1500 8150;;;;9400;;;;15.96;;;27775; 29025;;;;5.17
WARD;1250 9400;;;;9400;;;;;13.3;;;29025; 29025;;;;4.31
已选择14行。 ; 4.来一个综合的例子，求和规则有按部门分区的，有不分区的例子
SQL> select deptno,ename,sal,sum(sal) over (partition by deptno order by sal) dept_sum,

; 2; sum(sal) over (order by deptno,sal) sum
3; from emp; ;DEPTNO ENAME;;SALDEPT_SUM;;;;;SUM
---------- ---------- ---------- ---------- ----------
10 MILLER1300;;;;1300;;;;1300
CLARK;2450;;;;3750;;;;3750
KING;;5000;;;;8750;;;;8750 ;;;;;20 SMITH;;800;;;;;800;;;;9550
ADAMS;1100;;;;1900;;;10650
JONES;2975;;;;4875;;;13625
SCOTT;3000;;;10875;;;19625
FORD;;3000;;;10875;;;19625 ;;;;;30 JAMES;;950;;;;;950;;;20575
WARD;;1250;;;;3450;;;23075
MARTIN1250;;;;3450;;;23075
TURNER1500;;;;4950;;;24575
ALLEN;1600;;;;6550;;;26175

BLAKE;2850;;;;9400;;;29025
已选择14行。 ; 5.来一个逆序的，即部门从大到小排列，部门里各员工的薪水从高到低排列，累计和的规则不变。 SQL> select deptno,ename,sal,
2; sum(sal) over (partition by deptno order by deptno desc,sal desc) dept_sum,
3; sum(sal) over (order by deptno desc,sal desc) sum
4; from emp; ;DEPTNO ENAME;;SALDEPT_SUM;;;;;SUM
---------- ---------- ---------- ---------- ----------
30 BLAKE;2850;;;;2850;;;;2850
ALLEN;1600;;;;4450;;;;4450
TURNER1500;;;;5950;;;;5950
WARD;;1250;;;;8450;;;;8450
MARTIN1250;;;;8450;;;;8450
JAMES;;950;;;;9400;;;;9400 ;;;;;20 SCOTT;3000;;;;6000;;;15400
FORD;;3000;;;;6000;;;15400
JONES;2975;;;;8975;;;18375
ADAMS;1100;;;10075;;;19475
SMITH;;800;;;10875;;;20275 ;;;;;10 KING;;5000;;;;5000;;;25275

CLARK;2450;;;;7450;;;27725
MILLER1300;;;;8750;;;29025
已选择14行。 ; 6.体会：在"... from emp;"后面不要加order; by 子句，使用的分析函数的(partition by deptno order by sal)
里已经有排序的语句了，假如再在句尾添加排序子句，一致倒罢了，不一致，结果就令人费劲了。如： SQL> select deptno,ename,sal,sum(sal) over (partition by deptno order by sal) dept_sum,
2; sum(sal) over (order by deptno,sal) sum
3; from emp
4; order by deptno desc; ;DEPTNO ENAME;;SALDEPT_SUM;;;;;SUM
---------- ---------- ---------- ---------- ----------
30 JAMES;;950;;;;;950;;;20575
WARD;;1250;;;;3450;;;23075
MARTIN1250;;;;3450;;;23075
TURNER1500;;;;4950;;;24575
ALLEN;1600;;;;6550;;;26175
BLAKE;2850;;;;9400;;;29025 ;;;;;20 SMITH;;800;;;;;800;;;;9550
ADAMS;1100;;;;1900;;;10650
JONES;2975;;;;4875;;;13625

SCOTT;3000;;;10875;;;19625
FORD;;3000;;;10875;;;19625 ;;;;;10 MILLER1300;;;;1300;;;;1300
CLARK;2450;;;;3750;;;;3750
KING;;5000;;;;8750;;;;8750
已选择14行。