案例:查询出部门中所有人的平均薪资等级
背景:当前数据库的department表、salgrade表、employee表,数据分别如下:
情景1、查询部门中所有人的平均薪水的等级
第一步:先查询出每个部门的平均薪资,起别名t ,用作临时表(一会放入子查询)
-- 查询出每个部门的平均薪资,起别名t ,用作临时表(一会放入子查询) SELECT e.departmentid,AVG(e.salary) as avgsal FROM employee as e GROUP BY departmentid;
结果图:
最终SQL代码:再为 t 表里的平均薪资查询出对应的薪资等级
-- 用查询出来的每个部门平均薪资表t,去匹配薪资等级表,找出每个部门的平均薪资是属于什么薪资等级 SELECT t.departmentid,s.grade FROM (SELECT e.departmentid,AVG(e.salary) as avgsal FROM employee as e GROUP BY e.departmentid) t INNER JOIN salgrade s on t.avgsal BETWEEN s.losal and s.hisal ORDER BY t.departmentid;
最终查询后的结果图:
情景2、查询部门中所有人的薪资等级的平均等级
第一步:先查出每个部门里员工对应的薪资等级,起别名为 t ,用作临时表(一会用作子查询)
SELECT e.*,s.grade FROM employee e INNER JOIN salgrade s ON e.salary BETWEEN s.losal and s.hisal ORDER BY departmentid;
结果图:
最终SQL语句:再对 t 表里的每个部门员工的薪资等级求均值
SELECT t.departmentid,AVG(t.grade) as avgra FROM (SELECT e.*,s.grade FROM employee e INNER JOIN salgrade s ON e.salary BETWEEN s.losal and s.hisal ORDER BY departmentid ) t GROUP BY t.departmentid;
结果图: