oracle多表查询（四）

范例：要求统计出每个部门的详细信息，并且要求这些部门的平均工资高于2000；

在以上程序的基础上完成开发，在之前唯一所学习的限定查询的语法只有WHERE子句，所以下面先使用WHERE完成要求。

 

 

SELECT d.DEPTNO 部门编号,d.DNAME 部门名称,d.LOC 部门位置, coalesce(trunc(avg(e.SAL)),0) 平均薪资
FROM EMP e,DEPT d WHERE e.DEPTNO=d.DEPTNO AND coalesce(trunc(avg(e.SAL)),0)>2000 GROUP BY d.DEPTNO,d.DNAME,d.LOC

where的子句中是不允许出现分组函数的，这主要是因为where是从所有数据中第一步筛选。

例如，我们对动物进行分类，先用where找出在陆地上生存的，再用group by分出哪些是爬的，哪些是走的。如果需要对group by之后的数据再进行筛选就需要使用having

刚才的查询应该这么写：

SELECT d.DEPTNO 部门编号,d.DNAME 部门名称,d.LOC 部门位置, coalesce(trunc(avg(e.SAL)),0) 平均薪资
FROM EMP e,DEPT d WHERE e.DEPTNO=d.DEPTNO  GROUP BY d.DEPTNO,d.DNAME,d.LOC HAVING coalesce(trunc(avg(e.SAL)),0)>2000

where和having的区别：

where是在group by之前进行筛选，表示对全部数据进行筛选，在where中不能使用统计函数

having是在group by之后进行筛选，可以使用统计函数

 思考题：显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于$4000，输出结果按月工资的合计升序排列：

SELECT sum(SAL) 工资合计,job 工作名称 FROM EMP WHERE JOB IN (SELECT JOB FROM EMP WHERE JOB!='SALESMAN') 
GROUP BY JOB HAVING sum(sal)>4000 ORDER BY 1;

 

刚才的查询用到了 in 关键字，这里就要讲一下子查询， 子查询相当于简单查询+限定查询+多表查询+统计查询的综合体，效率比较低，但是这是替代多表查询最好的方式。

范例：要求查询出工资比WARD还要高的全部雇员信息

select e.ename FROM EMP e  WHERE e.SAL>(SELECT SAL FROM EMP WHERE ENAME='WARD') AND e.ENAME!='WARD';

 

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

SELECT d.DEPTNO 部门编号,d.DNAME 部门名称,d.LOC 部门位置,count(e.EMPNO) 部门人数,coalesce( trunc(avg(e.SAL)),0) 平均工资
FROM EMP e,DEPT d WHERE e.DEPTNO(+)=d.DEPTNO GROUP BY d.DEPTNO,d.DNAME,d.LOC

学习了子查询以后可以这么写

select d.DEPTNO 部门编号,d.DNAME 部门名称,d.LOC 部门位置,coalesce(temp.avg,0) 平均薪资,coalesce(temp.count,0) 部门人数
FROM DEPT d,(SELECT coalesce(trunc(avg(SAL)),0) avg,count(EMPNO) count,DEPTNO FROM EMP GROUP BY DEPTNO) temp
WHERE temp.DEPTNO(+)=d.DEPTNO