原始emp表中的数据如下
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------ ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
1.统计每个部门的最高工资,最低工资和平均工资.
select '最高工资' "统计",
max(decode(deptno,10,sal,null)) "10部门",
max(decode(deptno,20,sal,null)) "20部门",
max(decode(deptno,30,sal,null)) "30部门",
max(sal) "所有部门"
from emp
group by 1
union all
select '最低工资' "统计",
min(decode(deptno,10,sal,null)) "10部门",
min(decode(deptno,20,sal,null)) "20部门",
min(decode(deptno,30,sal,null)) "30部门",
min(sal) "所有部门"
from emp
group by 1
union all
select '平均工资' "统计",
trunc(avg(decode(deptno,10,sal,null)),2) "10部门",
trunc(avg(decode(deptno,20,sal,null)),2) "20部门",
trunc(avg(decode(deptno,30,sal,null)),2) "30部门",
trunc(avg(sal),2) "所有部门"
from emp
group by 1
;
查询效果如下:
统计 10部门 20部门 30部门 所有部门
-------- ---------- ---------- ---------- ----------
最高工资 5000 3000 2850 5000
最低工资 1300 800 950 800
平均工资 2916.66 2175 1566.66 2073.21
2.按照工资段进行人数统计
select '统计' "人数",
count(decode(trunc((sal-1)/1500,0),0,sal,null)) "<=1500",
count(decode(trunc((sal-1)/1500,0),1,sal,null)) "1501-3000",
count(decode(trunc((sal-1)/1500,0),0,null,1,null,sal)) "其他"
from emp;
查询效果如下:
---- ---------- ---------- ----------
统计 7 6 1
3.按照hiredate分类统计
select '人数'"统计",
count(decode(to_char(hiredate,'YY'),80,hiredate,null)) "80",
count(decode(to_char(hiredate,'YY'),81,hiredate,null)) "81",
count(decode(to_char(hiredate,'YY'),80,null,81,null,hiredate)) "其他"
from emp;
查询效果如下:
统计 80 81 其他
---- ---------- ---------- ----------
人数 1 10 3