参考编写代码

示例三

按部门统计员工人数，按照如下格式输出

所有员工入职日期

select hiredate from emp

1、使用函数方式

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;

2、使用子查询方式

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;

示例2

找到员工表中薪水大于本部门平均薪水的员工

1、相关子查询

select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal from emp e where sal >(select avg(sal) from emp where deptno=e.deptno);

-------------------------------------------

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;

---------------------------------------------

写执行计划

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);

用相关子查询方式更省资源

分页查询显示员工信息：显示员工号，姓名，月薪

-每页显示四条记录

-显示第二页的员工

-按照月薪降序排列

----------------------------------------------------------------

rownum 行号（顺序是默认的，只能使用<,<=,不能使用>,>=）

e1 表,按照工资降序排列顺序

select rownum,empno,ename,sal from emp order by sal desc

-----------------------------------------

e2 表 1-8号的员工按照员工工资降序排列

select rownum ,empno,ename,sal from (select rownum ,empno,ename,sal from emp order by sal desc) e1 where rownum <=8) e2

--------------------------------------------

rownum r ,r是e2表的第一列，e1表的行号

e3 表 为8-5号员工按薪水降序排列

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;

子查询中的NULL值问题

1、单行子查询中的NULL值问题（子查询不返回任何行）

2、多行子查询中的NULL值问题

查询不是老板的员工

select * from emp where empno not in (select mgr from emp where mgr is not null);

单行子查询和多行子查询

1、单行子查询只能使用单行操作符；多行子查询只能使用多行操作符

单行子查询示例

1、查询员工信息，要求：-职位与7566员工一样-薪水大于7782员工的薪水

select * from emp where job= (select job from emp where empno=7566) and sal>(select sal from emp where empno=7782);

单行子查询只能使用单行操作符

在一个主查询中可以有多个子查询

2、查询工资最低的员工信息

select * from emp where sal =(select min(sal) from emp );

3、查询最低工资大于20号部门最低工资的部门号和部门的最低工资

select deptno,min(sal) from emp group by deptno having min(sal) > (select min(sal) from emp where deptno=20);

多行子查询

1、IN 

查询部门名称是SALES和ACCOUNTING的员工信息

select * from emp where deptno in (select deptno from dept where dname ='SALES' or dname='ACCOUNTING');

select e.* from emp e,dept d where e.deptno=d.deptno and (d.dname='SALES' or d.dname='ACCOUNTING');

2、ANY

查询工资比30号部门任意一个员工高的员工信息

select * from emp where sal >any (select sal fro emp where deptno=30);

等价于

select * from emp where sal >(select min(sal) from emp where deptno=30);

3、ALL

查询工资比30号部门所有员工高的员工信息

select * from emp where sal >all(select sal from emp where deptno=30);

等价于

select * from emp where sal >  (select max(sal) from emp where deptno=30);

主查询和子查询的执行顺序

1、一般先执行子查询，再执行主查询；但相关子查询例外

找到员工表中薪水大于本部门平均薪水的员工

select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal from emp e where sal> (select avg(sal) from emp where deptno=e.deptno);

子查询的排序问题

一般不在子查询中，使用排序；但在Top-N分析问题中，必须对子查询排序

找到员工表中工资最高的前三名

---rownum 行号

1、行号永远按照默认的顺序生成

2、行号只能使用<,<=;不能使用>,>=

select rownum,empno,ename,sal from emp;

select rownum,empno,ename,sal from emp order by sal desc;

select rownum,empon,ename,sal from (select * from emp order by sal desc) where rownum <=3;

主查询和子查询可以不是同一张表

1、查询部门名称是SALES的员工信息

多表查询

select e.* from emp e,dept d where e.deptno=d.deptno and d.dname='SALES'

FROM 后面的子查询

1、查询员工信息：员工号、姓名、月薪

select * from (select empno,ename,sal from emp);

2、查询员工信息：员工号、姓名、月薪、年薪

select * from (select empno,ename ,sal,sal*12 年薪 from emp);

不可以使用子查询的位置：group by

原来子查询可以用来解决排序乱的问题

不懂 说的是什么 意义是什么

直接跳过where的子查询还真行

select 子查询只能有一行，不行的话就把它变成一行