最近学习Oracle,老师讲了三个比较典型的问题,做一下总结,也便于以后复习.
下图显示的是三个题的题干和要查询的结果:
===========================================第一题============================
找到员工表中工资最高的前三名:先说明一个现象:
1 SQL> select rownum,ename from emp; 2 3 ROWNUM ENAME 4 ---------- ---------- 5 1 SMITH 6 2 ALLEN 7 3 WARD 8 4 JONES 9 5 MARTIN10 6 BLAKE11 7 CLARK12 8 SCOTT13 9 KING14 10 TURNER15 11 ADAMS16 12 JAMES17 13 FORD18 14 MILLER
如果在加上order by排序的条件限制:
1 SQL> select rownum ,ename,sal from emp order by sal desc; 2 3 ROWNUM ENAME SAL 4 ---------- ---------- ---------- 5 9 KING 5000 6 13 FORD 3000 7 8 SCOTT 3000 8 4 JONES 2975 9 6 BLAKE 285010 7 CLARK 245011 2 ALLEN 160012 10 TURNER 150013 14 MILLER 130014 3 WARD 125015 5 MARTIN 125016 11 ADAMS 110017 12 JAMES 95018 1 SMITH 800
加上order by 之后rownum 依旧没有变化,说明rownum是基于原始表emp进行排序的 ,固定住了,所以如下使用rownum<=3 来取得Top3是错误的:
SQL> select rownum,empno,ename,sal 2 from emp 3 where rownum<=3 4 order by sal desc; ROWNUM EMPNO ENAME SAL ---------- ---------- ---------- ---------- 2 7499 ALLEN 1600 3 7521 WARD 1250 1 7369 SMITH 800
只要是表中的数据内容没有变,每条记录对应的行号rownum就不会变....
这个地方可以引出Oracle中的分页问题:
具体可以看我在博客中的总结:
把这个select ename,sal from emp order by sal desc查询的结果集再看成一个新表
用rownum就会对这个新表进行排序.排序的rownum按照查询的顺序来展示.
select rownum,ename,sal from (select ename,sal from emp order by sal desc) where rownum < 4 ROWNUM ENAME SAL---------- ---------- ---------- 1 KING 5000 2 SCOTT 3000 3 FORD 3000
===========================================第二题===================================
找到员工表中薪水大于本部门平均薪水的员工
SQL> select e.empno,e.ename,e.sal,d.avgsal from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d where e.deptno=d.deptno and e.sal>d.avgsal; EMPNO ENAME SAL AVGSAL---------- ---------- ---------- ---------- 7499 ALLEN 1600 1566.66667 7566 JONES 2975 2175 7698 BLAKE 2850 1566.66667 7788 SCOTT 3000 2175 7839 KING 5000 2916.66667 7902 FORD 3000 2175
e.deptno = empavg.deptno and e.sal > empavg.salavg
翻译下来就是:第一个条件是本部门的员工,第二个是薪水大于部门的平均薪水还有一种不用group by分组函数也能查询到
叫:相关子查询
相关子查询:将主查询中的某些值作为参数传递给子查询.
相关子查询对外边的主表有一个要求,必须有一个别名.
先不看上面结果的第四列(AVGSAL),可以很容易的查询到
select empno,ename, sal from emp e
然后查询某个部门的平均工资:
select avg(sal) from emp where deptno = ?
需要确定这个deptno是什么!
结合上面两个语句
select empno,ename, sal from emp e
where e.sal >( select avg(sal) from emp where deptno = ?)
这个问好?应该代表的是查询的当前员工所在的部门号传给这个问号.
变成:
select empno,ename, sal from emp e
where e.sal > (select avg(sal) from emp where deptno = e.deptno);
然后主查询列上要加上所在部门平均工资
把上面的最后一句查询部门的平均工资拷贝到主查询列上.就可以了.
SQL> select empno,ename, sal,(select avg( sal) from emp where deptno = e.deptno) avgsal from emp e where e.sal>(select avg( sal) from emp where deptno = e.deptno); EMPNO ENAME SAL AVGSAL---------- ---------- ---------- ---------- 7499 ALLEN 1600 1566.66667 7566 JONES 2975 2175 7698 BLAKE 2850 1566.66667 7788 SCOTT 3000 2175 7839 KING 5000 2916.66667 7902 FORD 3000 2175
===============================================第三题=======================
统计每年入职的员工的个数!select count(*) from emp where decode(substr(to_char(hiredate,'yyyy-mm-dd'),3,2)80decode(substr(to_char(hiredate,"yyyy-mm-dd"),3,4),'81',count+1)SQL> select count(*) ,2 sum(decode(to_char(hiredate,'RR'),'80',1,0)) "1980",3 sum(decode(to_char(hiredate,'RR'),'81',1,0)) "1981",4 sum(decode(to_char(hiredate,'RR'),'87',1,0)) "1987"5 from emp;COUNT(*) 1980 1981 1987---------- ---------- ---------- ----------14 1 10 2