动力节点的MySQL的34题目的第7题的我的参考答案

以下是：薪水的平均等级最低的部门的名称

select 
t4.t4deptno,t4.t4grade,d1.dname
from(
##求出各部门平均等级begin
select avg(t3.t3grade) t4grade,t3.t3deptno t4deptno
from
#求出各人薪资等级begin
(select e2.ename, e2.sal,e2.deptno as t3deptno,s2.grade as t3grade
from emp e2
join salgrade s2
on e2.sal between s2.losal and s2.hisal) as t3
#求出各人薪资等级end
group by t4deptno
) as t4
join dept d1
on t4.t4deptno=d1.deptno
where t4.t4grade=(
###求出最低的平均等级begin
select 
min(t2grade) mymin
from(
##求出各部门平均等级begin
select avg(t1.t1grade) t2grade,t1.t1deptno t2deptno
from
#求出各人薪资等级begin
(select e1.ename, e1.sal,e1.deptno as t1deptno,s1.grade as t1grade
from emp e1
join salgrade s1
on e1.sal between s1.losal and s1.hisal) as t1
#求出各人薪资等级end
group by t1.t1deptno
##求出各部门平均等级end
) as t2
###求出最低的平均等级end
);

以下是：求平均薪水的等级最低的部门的名称

#再求出最低等级对应的部门
select
d1.dname,t4.t4grade,t4.t4deptno,t4.t4sal
from(
#再求出各部门平均薪水等级
select s2.grade t4grade,t3.deptno t4deptno,t3.t3sal t4sal
from(
#再求出各部门平均薪水
select avg(sal) as t3sal,deptno
from emp
group by deptno )as t3
join salgrade s2
on t3.t3sal between s2.losal and s2.hisal
where s2.grade=(
#求出最小的等级
select min(t2grade)
from(
#求出对应的等级
select s1.grade t2grade,t1.deptno,t1.t1sal
from(
#先求出各部门平均薪水
select avg(sal) t1sal,deptno
from emp
group by deptno) as t1
#先求出各部门平均薪水end
join salgrade s1
on t1.t1sal between s1.losal and s1.hisal) as t2
)#求出对应的等级end
) as t4
join dept d1
on d1.deptno=t4.t4deptno;