一、取得平均薪水最高的部门的部门编号
第一步:求出部门的平均薪水
select e.deptno, avg(e.sal) as avgsal from emp e group by e.deptno;
deptnoavgsal
102916.666667
202715.000000
301566.666667
第二步:将以上查询结果当成临时表t(deptno, avgsal),查询平均薪水的最高值
select max(t.avgsal) as maxAvgSal from t;
将t指向上述查询结果:
select max(t.avgsal) as maxAvgSal from (select e.deptno, avg(e.sal) as avgsal from emp e group by e.deptno) t;
maxAvgSal
2916.666667
第三步:过滤第一步的查询结果,将第二步查询到的最高值代入:
select e.deptno, avg(e.sal) as avgsal from emp e group by e.deptno having avgsal = 2916.666667;
转换成:
select e.deptno, avg(e.sal) as avgsal from emp e group by e.deptno having avgsal =
(select max(t.avgsal) as maxAvgSal from (select e.deptno, avg(e.sal) as avgsal from emp e group by e.deptno) t);