1、分页查询
--查询员工表中(5,8]页的记录 select rownum,r,empno,ename,sal from ( select rownum r , empno,ename,sal from (select rownum,empno,ename,sal from emp order by sal desc) e1 where rownum<=8) e2 where r > 5
2.查询员工表中薪水大于本部门平均薪水的员工
--多表查询 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 select * from table(dbms_xplan.display)
--相关子查询 select empno,ename,sal,(select avg(sal) avgsal from emp where deptno = e.deptno) from emp e where sal > ( select avg(sal) avgsal from emp where deptno = e.deptno )
注意:在SQL语句前加 explain plan for
explain plan for 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
再执行命令 select * from table(dbms_xplan.display) 查看两种方法的资源占用大小。最后对比发现,相关子查询更好
3、统计每个年份入职员工人数的和
--方法一 函数 select count(*) total , sum(decode(to_char(hiredate,'YYYY'),'1980',1,0)) "1980", sum(decode(to_char(hiredate,'YYYY'),'1981',1,0)) "1981", sum(decode(to_char(hiredate,'YYYY'),'1982',1,0)) "1982", sum(decode(to_char(hiredate,'YYYY'),'1987',1,0)) "1987" from emp; --方法二 select (select count(*) from emp) Total, (select count(*) from emp where to_char(hiredate,'YYYY')='1980') "1980" , (select count(*) from emp where to_char(hiredate,'YYYY')='1981') "1981" , (select count(*) from emp where to_char(hiredate,'YYYY')='1982') "1982" , (select count(*) from emp where to_char(hiredate,'YYYY')='1987') "1987" from dual;
--慕课学习笔记