环境：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
MILLER
1300
20 SMITH
800
ADAMS
1100
FORD
3000
SCOTT
3000
JONES
2975
30 ALLEN
1600
BLAKE
2850
MARTIN
1250
JAMES
950
TURNER
1500
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 MARTIN
1250
21175
29025
4.31
10 MILLER
1300
22475
29025
4.48
20 SCOTT
3000
25475
29025
10.34
20 SMITH
800
26275
29025
2.76
30 TURNER
1500
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 CLARK
2450
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 ADAMS
1100
1100
10875
10.11
9850
29025
3.79
FORD
3000
4100
10875
27.59
12850
29025
10.34
JONES
2975
7075
10875
27.36
15825
29025
10.25
SCOTT
3000
10075
10875
27.59
18825
29025
10.34
SMITH
800
10875
10875
7.36
19625
29025
2.76
30 ALLEN
1600
1600
9400
17.02
21225
29025
5.51
BLAKE
2850
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
SAL
DEPT_SUM
SUM
---------- ---------- ---------- ---------- ----------
10 MILLER
1300
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
MARTIN
1250
3450
23075
TURNER
1500
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
SAL
DEPT_SUM
SUM
---------- ---------- ---------- ---------- ----------
30 BLAKE
2850
2850
2850
ALLEN
1600
4450
4450
TURNER
1500
5950
5950
WARD
1250
8450
8450
MARTIN
1250
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
MILLER
1300
8
