1 取出emp表汇总每个员工的部门号,姓名,员工工资已经部门的平均工资

方法一(分析函数):
SQL> select deptno,ename,sal,avg(sal) over (partition by deptno) avg_sal from emp;

DEPTNO ENAME   SAL  AVG_SAL
---------- ---------- ---------- ---------- 
10 CLARK     2450 2916.66667
10 KING      5000 2916.66667
10 MILLER     1300 2916.66667
20 JONES     2975    2175
20 FORD      3000    2175
20 ADAMS     1100    2175
20 SMITH      800    2175
20 SCOTT     3000    2175
30 WARD      1250 1566.66667
30 TURNER     1500 1566.66667
30 ALLEN     1600 1566.66667
30 JAMES      950 1566.66667
30 BLAKE     2850 1566.66667
30 MARTIN     1250 1566.66667

14 rows selected.

方法二(表连接):
SQL> select a.deptno,a.ename,a.sal,b.avg_sal from emp a,(select deptno,avg(sal) avg_sal from emp group by deptno) b where a.deptno=b.deptno order by deptno;

DEPTNO ENAME   SAL  AVG_SAL
---------- ---------- ---------- ---------- 
10 CLARK     2450 2916.66667
10 KING      5000 2916.66667
10 MILLER     1300 2916.66667
20 SMITH      800    2175
20 JONES     2975    2175
20 SCOTT     3000    2175
20 ADAMS     1100    2175
20 FORD      3000    2175
30 ALLEN     1600 1566.66667
30 WARD      1250 1566.66667
30 MARTIN     1250 1566.66667
30 BLAKE     2850 1566.66667
30 TURNER     1500 1566.66667
30 JAMES      950 1566.66667

14 rows selected.

2 显示各部门员工的工资,并附带显示该部分的最高工资。

方法一(分析函数):
SQL> select deptno,ename,sal,max(sal) over (partition by deptno) max_sal from emp;

DEPTNO ENAME       SAL  MAX_SAL
---------- ---------- ---------- ---------- 
10 CLARK      2450    5000
10 KING       5000    5000
10 MILLER      1300    5000
20 JONES      2975    3000
20 FORD       3000    3000
20 ADAMS      1100    3000
20 SMITH       800    3000
20 SCOTT      3000    3000
30 WARD       1250    2850
30 TURNER      1500    2850
30 ALLEN      1600    2850
30 JAMES       950    2850
30 BLAKE      2850    2850
30 MARTIN      1250    2850

14 rows selected.

当省略窗口子句时:
a) 如果存在order by则默认的窗口是unbounded preceding and current row --当前组的第一行到当前行
b) 如果同时省略order by则默认的窗口是unbounded preceding and unbounded following --整个组

SQL> select deptno,ename,sal,max(sal) over (partition by deptno order by ename) max_sal from emp;

DEPTNO ENAME       SAL  MAX_SAL
---------- ---------- ---------- ---------- 
10 CLARK      2450    2450
10 KING       5000    5000
10 MILLER      1300    5000
20 ADAMS      1100    1100
20 FORD       3000    3000
20 JONES      2975    3000
20 SCOTT      3000    3000
20 SMITH       800    3000
30 ALLEN      1600    1600
30 BLAKE      2850    2850
30 JAMES       950    2850
30 MARTIN      1250    2850
30 TURNER      1500    2850
30 WARD       1250    2850

14 rows selected.

当省略窗口子句时:
a) 如果存在order by则默认的窗口是unbounded preceding and current row --当前组的第一行到当前行
b) 如果同时省略order by则默认的窗口是unbounded preceding and unbounded following --整个组

SQL> select deptno,ename,sal,max(sal) over (partition by deptno order by ename rows between unbounded preceding and unbounded following) max_sal from emp;

DEPTNO ENAME       SAL  MAX_SAL
---------- ---------- ---------- ---------- 
10 CLARK      2450    5000
10 KING       5000    5000
10 MILLER      1300    5000
20 ADAMS      1100    3000
20 FORD       3000    3000
20 JONES      2975    3000
20 SCOTT      3000    3000
20 SMITH       800    3000
30 ALLEN      1600    2850
30 BLAKE      2850    2850
30 JAMES       950    2850
30 MARTIN      1250    2850
30 TURNER      1500    2850
30 WARD       1250    2850

14 rows selected.

方法二(表连接)
SQL> select a.deptno,a.ename,a.sal,b.max from emp a,(select deptno,max(sal) max from emp group by deptno) b where a.deptno=b.deptno order by deptno;

DEPTNO ENAME       SAL    MAX
---------- ---------- ---------- ---------- 
10 CLARK      2450    5000
10 KING       5000    5000
10 MILLER      1300    5000
20 SMITH       800    3000
20 JONES      2975    3000
20 SCOTT      3000    3000
20 ADAMS      1100    3000
20 FORD       3000    3000
30 ALLEN      1600    2850
30 WARD       1250    2850
30 MARTIN      1250    2850
30 BLAKE      2850    2850
30 TURNER      1500    2850
30 JAMES       950    2850

14 rows selected.

3 对各部门进行分组,并附带显示第一行至当前行的汇总

SQL> select deptno,ename,sal,sum(sal) over(partition by deptno order by ename rows between unbounded preceding and current row) sum_sal from emp;

--注意ROWS BETWEEN unbounded preceding AND current row 是指第一行至当前行的汇总

DEPTNO ENAME       SAL  SUM_SAL
---------- ---------- ---------- ---------- 
10 CLARK      2450    2450
10 KING       5000    7450
10 MILLER      1300    8750
20 ADAMS      1100    1100
20 FORD       3000    4100
20 JONES      2975    7075
20 SCOTT      3000   10075
20 SMITH       800   10875
30 ALLEN      1600    1600
30 BLAKE      2850    4450
30 JAMES       950    5400
30 MARTIN      1250    6650
30 TURNER      1500    8150
30 WARD       1250    9400

14 rows selected.

4 对各部门进行分组,并附带显示当前行至最后一行的汇总

SQL> select deptno,ename,sal,sum(sal) over(partition by deptno order by ename rows between current row and unbounded following) max from emp;

--注意ROWS BETWEEN current row AND unbounded following 指当前行到最后一行的汇总

DEPTNO ENAME       SAL    MAX
---------- ---------- ---------- ---------- 
10 CLARK      2450    8750
10 KING       5000    6300
10 MILLER      1300    1300
20 ADAMS      1100   10875
20 FORD       3000    9775
20 JONES      2975    6775
20 SCOTT      3000    3800
20 SMITH       800    800
30 ALLEN      1600    9400
30 BLAKE      2850    7800
30 JAMES       950    4950
30 MARTIN      1250    4000
30 TURNER      1500    2750
30 WARD       1250    1250

14 rows selected.

5 对各部门进行分组,并附带显示当前行的上一行(rownum-1)到当前行的汇总

SQL> select deptno,ename,sal,sum(sal) over(partition by deptno order by ename rows between 1 preceding and current row) max from emp;

--注意ROWS BETWEEN 1 preceding AND current row 是指当前行的上一行(rownum-1)到当前行的汇总

DEPTNO ENAME       SAL    MAX
---------- ---------- ---------- ---------- 
10 CLARK      2450    2450
10 KING       5000    7450
10 MILLER      1300    6300
20 ADAMS      1100    1100
20 FORD       3000    4100
20 JONES      2975    5975
20 SCOTT      3000    5975
20 SMITH       800    3800
30 ALLEN      1600    1600
30 BLAKE      2850    4450
30 JAMES       950    3800
30 MARTIN      1250    2200
30 TURNER      1500    2750
30 WARD       1250    2750

14 rows selected.

6 对各部门进行分组,并附带显示当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的汇总

SQL> select deptno,ename,sal,sum(sal) over(partition by deptno order by ename rows between 1 preceding and 2 following) max from emp;

--注意ROWS BETWEEN 1 preceding AND 2 following 是指当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的汇总

DEPTNO ENAME       SAL    MAX
---------- ---------- ---------- ---------- 
10 CLARK      2450    8750
10 KING       5000    8750
10 MILLER      1300    6300
20 ADAMS      1100    7075
20 FORD       3000   10075
20 JONES      2975    9775
20 SCOTT      3000    6775
20 SMITH       800    3800
30 ALLEN      1600    5400
30 BLAKE      2850    6650
30 JAMES       950    6550
30 MARTIN      1250    4950
30 TURNER      1500    4000
30 WARD       1250    2750

14 rows selected.

SQL> select deptno,ename,sal,last_value(sal) over(partition by deptno) sum from emp;

DEPTNO ENAME       SAL    SUM
---------- ---------- ---------- ---------- 
10 CLARK      2450    1300
10 KING       5000    1300
10 MILLER      1300    1300
20 JONES      2975    3000
20 FORD       3000    3000
20 ADAMS      1100    3000
20 SMITH       800    3000
20 SCOTT      3000    3000
30 WARD       1250    1250
30 TURNER      1500    1250
30 ALLEN      1600    1250


DEPTNO ENAME       SAL    SUM
---------- ---------- ---------- ---------- 
30 JAMES       950    1250
30 BLAKE      2850    1250
30 MARTIN      1250    1250


14 rows selected.

SQL> select deptno,ename,sal,last_value(sal) over(partition by deptno order by sal desc) sum from emp;

DEPTNO ENAME       SAL    SUM
---------- ---------- ---------- ---------- 
10 KING       5000    5000
10 CLARK      2450    2450
10 MILLER      1300    1300
20 SCOTT      3000    3000
20 FORD       3000    3000
20 JONES      2975    2975
20 ADAMS      1100    1100
20 SMITH       800    800
30 BLAKE      2850    2850
30 ALLEN      1600    1600
30 TURNER      1500    1500


DEPTNO ENAME       SAL    SUM
---------- ---------- ---------- ---------- 
30 MARTIN      1250    1250
30 WARD       1250    1250
30 JAMES       950    950


14 rows selected.

SQL> select deptno,ename,sal,first_value(sal) over(partition by deptno) sum from emp;

DEPTNO ENAME       SAL    SUM
---------- ---------- ---------- ---------- 
10 CLARK      2450    2450
10 KING       5000    2450
10 MILLER      1300    2450
20 JONES      2975    2975
20 FORD       3000    2975
20 ADAMS      1100    2975
20 SMITH       800    2975
20 SCOTT      3000    2975
30 WARD       1250    1250
30 TURNER      1500    1250
30 ALLEN      1600    1250


DEPTNO ENAME       SAL    SUM
---------- ---------- ---------- ---------- 
30 JAMES       950    1250
30 BLAKE      2850    1250
30 MARTIN      1250    1250


14 rows selected.

Rank()

一 要求:同时取出语文成绩和数学成绩的前三名;

1:SQL> select * from t1 order by 1,2 desc;

SUBJECT               SCORE
------------------------------ ---------- 
chinese                120
chinese                88
chinese                77
chinese                70
chinese                65
matchs                150
matchs                100
matchs                 90
matchs                 80
matchs                 60

10 rows selected.

2:SQL> select rank() over(partition by subject order by score desc) rank,t1.* from t1;

RANK SUBJECT               SCORE
---------- ------------------------------ ---------- 
1 chinese                120
2 chinese                88
3 chinese                77
4 chinese                70
5 chinese                65
1 matchs                150
2 matchs                100
3 matchs                 90
4 matchs                 80
5 matchs                 60

10 rows selected.

3:SQL> select from (select rank() over(partition by subject order by score desc) rank,t1. from t1) a where a.rank<=3;

RANK SUBJECT               SCORE
---------- ------------------------------ ---------- 
1 chinese                120
2 chinese                88
3 chinese                77
1 matchs                150
2 matchs                100
3 matchs                 90

6 rows selected.

二 要求:取出分数最高的前三名

SQL> select from (select rank() over(order by score desc) rank,t1. from t1) a where a.rank<=3;

RANK SUBJECT               SCORE
---------- ------------------------------ ---------- 
1 matchs                150
2 chinese                120
3 matchs                100

三 要求:取出分数排在第五到第十名

SQL> select * from (select rank() over(order by score desc) rank,t1.* from t1) a where a.rank between 5 and 10; 

RANK SUBJECT               SCORE
---------- ------------------------------ ---------- 
5 chinese                88
6 matchs                 80
7 chinese                77
8 chinese                70
9 chinese                65
10 matchs                 60

6 rows selected.

四 要求:每个部门工资前三名

SQL> select * from (select deptno,ename,sal,rank() over(partition by deptno order by sal desc) rk from emp) a where a.rk<=3;

DEPTNO ENAME       SAL     RK
---------- ---------- ---------- ---------- 
10 KING       5000     1
10 CLARK      2450     2
10 MILLER      1300     3
20 SCOTT      3000     1
20 FORD       3000     1
20 JONES      2975     3
30 BLAKE      2850     1
30 ALLEN      1600     2
30 TURNER      1500     3

9 rows selected.

dense_rank()

dense_rank与rank()用法相当,但是有一个区别:dence_rank在并列关系是,相关等级不会跳过。rank则跳过

SQL> select * from (select deptno,ename,sal,dense_rank() over(partition by deptno order by sal desc) rk from emp) a where a.rk<=3;

DEPTNO ENAME       SAL     RK
---------- ---------- ---------- ---------- 
10 KING       5000     1
10 CLARK      2450     2
10 MILLER      1300     3
20 SCOTT      3000     1
20 FORD       3000     1
20 JONES      2975     2
20 ADAMS      1100     3
30 BLAKE      2850     1
30 ALLEN      1600     2
30 TURNER      1500     3

10 rows selected.

SQL> select * from (select deptno,ename,sal from emp order by sal desc) a where rownum<=5;

DEPTNO ENAME       SAL
---------- ---------- ---------- 
10 KING       5000
20 SCOTT      3000
20 FORD       3000
20 JONES      2975
30 BLAKE      2850
最后修改:2022 年 04 月 02 日
如果觉得我的文章对你有用,请随意赞赏