表与表的关联(二)
起的别名在having中不能用
select deptno,avg(sal) avg_sal from emp group by deptno
having avg(sal)=(select max(avg(sal)) from emp group by deptno)
--课堂练习：求平均薪水最高的部门的部门名称
--1,部门平均最高薪水
--2,得到部门编号列表,注意用group by deptno
--3,再应用having子句, having avg(sal) = (第一步的结果)
--4,得到平均最高薪水的那个部门的编号
--5,再得到部门名称
select * from dept where deptno =
(select deptno from emp group by deptno
having avg(sal)=(select max(avg(sal)) from emp group by deptno))
--求平均薪水的等级最低的部门的部门名称
--第一步：部门平均薪水的等级,分成两个小步骤,第一小步是求部门平均薪水
select t1.deptno,t1.avg_sal ,s.grade
from (select deptno, avg(sal) avg_sal from emp group by deptno ) t1
join salgrade s on t1.avg_sal between s.losal and s.hisal ---t1
www.2cto.com
--第二步：最低的等级值
select min(grade) from(
select t1.deptno,t1.avg_sal ,s.grade
from (select deptno, avg(sal) avg_sal from emp group by deptno ) t1
join salgrade s on t1.avg_sal between s.losal and s.hisal)
--第三步：等于最低值的部门编号
select *
from (select t1.deptno, t1.avg_sal, s.grade
from (select deptno, avg(sal) avg_sal from emp group by deptno) t1
join salgrade s on t1.avg_sal between s.losal and s.hisal) t1
where t1.grade =
(select min(grade)
from (select t1.deptno, t1.avg_sal, s.grade
from (select deptno, avg(sal) avg_sal
from emp www.2cto.com
group by deptno) t1
join salgrade s on t1.avg_sal between s.losal and s.hisal))
--第四步：求名称
select t.*,dname,loc from
(select *
from (select t1.deptno, t1.avg_sal, s.grade
from (select deptno, avg(sal) avg_sal from emp group by deptno) t1
join salgrade s on t1.avg_sal between s.losal and s.hisal) t1
where t1.grade =
(select min(grade)
from (select t1.deptno, t1.avg_sal, s.grade
from (select deptno, avg(sal) avg_sal
from emp
group by deptno) t1
join salgrade s on t1.avg_sal between s.losal and s.hisal))
)t join dept d on t.deptno=d.deptno
作者 黄方