本文共 714 字,大约阅读时间需要 2 分钟。
一、取得平均薪水最高的部门的部门编号
第一步:求出部门的平均薪水
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);
转载地址:https://blog.csdn.net/weixin_33305027/article/details/113648696 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!