子查询 在From子句中使用子查询

在From子句中使用子查询

范例：查询出每个部门的编号、名称、位置、部门人数、平均工资

第一步:查询出部门编号，人数，平均工资。

select deptno,count(empno),avg(sal)

from emp

group by deptno;

第二步：以上返回的数据是多行多列，当做一张数据表来处理，只要在from之后出现，引入dept表

select d.deptno,d.dname,d.loc,temp.count,temp.avg

from dept d ,(select deptno dno,count(empno) count,avg(sal) avg

from emp

group by deptno) temp

where d.deptno = temp.dno(+);

主要目的：子查询的出现主要是为了解决多表查询之中的性能问题。

次要目的：很多时候在From子句里面使用子查询，是因为在外部查询中无法再继续使用统计函数操作的时候。

范例：要求查询出高于公司平均工资的雇员姓名、职位、工资、部门名称、领导姓名、领导职位、部门人数和部门平均工资、工资等级以及此等级的雇员人数。

确定要使用的数据表：

emp表：统计公司的平均工资

emp表：雇员姓名、职位、工资

emp表：自身关联，领导姓名、领导职位

dept表：部门名称

emp表：统计部门人数、部门平均工资

salgrade表：工资等级

emp表：统计一个工资等级的人数

确定已知的关联字段：

雇员和领导：emp.mgr=m.empno

雇员和部门：emp.deptno=dept.deptno

第一步：计算出公司的平均工资，返回单行单列

select avg(sal) from emp;

第二步：找到高于此平均工资的雇员姓名、职位、工资

select e.ename,e.job,e.sal

from emp e

where e.sal >(select avg(sal) from emp );

第三步：领导姓名、领导职位

select e.ename,e.job,e.sal,m.ename,m.job

from emp e,emp m

where e.sal >(select avg(sal) from emp )

and e.mgr = m.empno(+);

第四步：加入dept表，找到部门名称

select e.ename,e.job,e.sal,m.ename,m.job,d.dname

from emp e,emp m,dept d

where e.sal >(select avg(sal) from emp )

and e.mgr = m.empno(+)

and e.deptno = d.deptno;

第五步：部门人数

select deptno dno,count(empno) count

from emp

group by deptno;

select e.ename,e.job,e.sal,m.ename,m.job,d.dname,dtemp.count

from emp e,emp m,dept d,(select deptno dno,count(empno) count

from emp

group by deptno) dtemp

where e.sal >(select avg(sal) from emp )

and e.mgr = m.empno(+)

and e.deptno = d.deptno

and d.deptno (+)= dtemp.dno;

第六步：工资等级，加入salgrade表

select e.ename,e.job,e.sal,m.ename,m.job,d.dname,dtemp.count,s.grade

from emp e,emp m,dept d,(select deptno dno,count(empno) count

from emp

group by deptno) dtemp，salgrade s

where e.sal >(select avg(sal) from emp )

and e.mgr = m.empno(+)

and e.deptno = d.deptno

and d.deptno (+)= dtemp.dno

and e.sal between s.losal and s.hisal;

第七步：找出工资等级的雇员人数，依然需要编写子查询进行统计

selects1.grade sg,count(e1.empno) count

from emp e1,salgrade s1

where e1.sal between s1.losal and s1.hisal

group by s1.grade;

select e.ename,e.job,e.sal,m.ename,m.job,d.dname,dtemp.count,s.grade,stemp.count

from emp e,emp m,dept d,(select deptno dno,count(empno) count

from emp

group by deptno) dtemp，salgrade s,(selects1.grade sg,count(e1.empno) count

from emp e1,salgrade s1

where e1.sal between s1.losal and s1.hisal

group by s1.grade) stemp

where e.sal >(select avg(sal) from emp )

and e.mgr = m.empno(+)

and e.deptno = d.deptno

and d.deptno (+)= dtemp.dno

and e.sal between s.losal and s.hisal

and s.grade = stemp.sg;