经典mysql sql语句面试题_经典MySQL的sql语句面试题

一、取得平均薪水最高的部门的部门编号

第一步：求出部门的平均薪水

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