--第一题 select e.ename 员工, e.sal 工资, sum(sal) over(partition by deptno) 部门工资总和, round(avg(sal) over(partition by deptno),2) 部门工资平均值 from emp e; 员工 工资 部门工资总和 部门工资平均值 ---------- --------- ------------ -------------- CLAR K 2450.00 7450 3725 KING 5000.00 7450 3725 SMIT H1 800.00 10875 2175 ADAM S 1100.00 10875 2175 FORD 3000.00 10875 2175 SCOT T 3000.00 10875 2175 JONE S 2975.00 10875 2175 ALLE N 1600.00 9400 1566.67 BLAK E 2850.00 9400 1566.67 MART IN 1250.00 9400 1566.67 JAME S 950.00 9400 1566.67 TURN ER 1500.00 9400 1566.67 WARD 1250.00 9400 1566.67 MILL ER 1300.00 1300 1300
14 rows selected
--第二题 select e.ename 员工, e.sal 工资, rank() over(partition by deptno order by sal desc)-1 同部门的高于员工工资的人数, rank() over(partition by deptno order by sal)-1 同部门的低于员工工资的人数 from emp e order by deptno;员工 工资 同部门的高于员工工资的人数 同部门的低于员工工资的人数 ---------- --------- -------------------------- -------------------------- KING 5000.00 0 1 CLARK 2450.00 1 0 SCOTT 3000.00 0 3 FORD 3000.00 0 3 JONES 2975.00 2 2 ADAMS 1100.00 3 1 SMITH1 800.00 4 0 BLAKE 2850.00 0 5 ALLEN 1600.00 1 4 TURNER 1500.00 2 3 WARD 1250.00 3 1 MARTIN 1250.00 3 1 JAMES 950.00 5 0 MILLER 1300.00 0 0
14 rows selected
--第二题 select e.ename 员工, e.deptno, e.sal 工资, dense_rank() over(partition by deptno order by sal desc) 所在部门的工资排名 from emp e order by deptno; 员工 DEPTNO 工资 所在部门的工资排名 ---------- ------ --------- ------------------ KING 10 5000.00 1 CLARK 10 2450.00 2 SCOTT 20 3000.00 1 FORD 20 3000.00 1 JONES 20 2975.00 2 ADAMS 20 1100.00 3 SMITH1 20 800.00 4 BLAKE 30 2850.00 1 ALLEN 30 1600.00 2 TURNER 30 1500.00 3 WARD 30 1250.00 4 MARTIN 30 1250.00 4 JAMES 30 950.00 5 MILLER 1300.00 1
--第一题
select e.ename 员工,
e.sal 工资,
sum(sal) over(partition by deptno) 部门工资总和,
round(avg(sal) over(partition by deptno),2) 部门工资平均值
from emp e;
员工 工资 部门工资总和 部门工资平均值
---------- --------- ------------ --------------
CLAR K 2450.00 7450 3725
KING 5000.00 7450 3725
SMIT H1 800.00 10875 2175
ADAM S 1100.00 10875 2175
FORD 3000.00 10875 2175
SCOT T 3000.00 10875 2175
JONE S 2975.00 10875 2175
ALLE N 1600.00 9400 1566.67
BLAK E 2850.00 9400 1566.67
MART IN 1250.00 9400 1566.67
JAME S 950.00 9400 1566.67
TURN ER 1500.00 9400 1566.67
WARD 1250.00 9400 1566.67
MILL ER 1300.00 1300 1300
14 rows selected
--第二题
select e.ename 员工,
e.sal 工资,
rank() over(partition by deptno order by sal desc)-1 同部门的高于员工工资的人数,
rank() over(partition by deptno order by sal)-1 同部门的低于员工工资的人数
from emp e order by deptno;员工 工资 同部门的高于员工工资的人数 同部门的低于员工工资的人数
---------- --------- -------------------------- --------------------------
KING 5000.00 0 1
CLARK 2450.00 1 0
SCOTT 3000.00 0 3
FORD 3000.00 0 3
JONES 2975.00 2 2
ADAMS 1100.00 3 1
SMITH1 800.00 4 0
BLAKE 2850.00 0 5
ALLEN 1600.00 1 4
TURNER 1500.00 2 3
WARD 1250.00 3 1
MARTIN 1250.00 3 1
JAMES 950.00 5 0
MILLER 1300.00 0 0
14 rows selected
--第二题
select e.ename 员工,
e.deptno,
e.sal 工资,
dense_rank() over(partition by deptno order by sal desc) 所在部门的工资排名
from emp e
order by deptno;
员工 DEPTNO 工资 所在部门的工资排名
---------- ------ --------- ------------------
KING 10 5000.00 1
CLARK 10 2450.00 2
SCOTT 20 3000.00 1
FORD 20 3000.00 1
JONES 20 2975.00 2
ADAMS 20 1100.00 3
SMITH1 20 800.00 4
BLAKE 30 2850.00 1
ALLEN 30 1600.00 2
TURNER 30 1500.00 3
WARD 30 1250.00 4
MARTIN 30 1250.00 4
JAMES 30 950.00 5
MILLER 1300.00 1