1.部门表结构如下:
解题思路:查询各个部门中工资高于本部门的平均工资的员工数和部门号,并按部门号排序
第一步:首先查询出部门的平均工资
select department_id,AVG(salary)
from employees
group by department_id
查询结果:
第二步:此时需要把第一步查询到的结果集作为新表去做连表查询,即:
select * from a,b where a.departmentid = b.departmentid 的形式,查出大于平均工资的记录。
select t1.*
from employees t1,
(select department_id,AVG(salary) avg_salary
from employees
group by department_id ) t2
where t1.department_id = t2.department_id and salary>t2.avg_salary
查询结果:
第三步:筛选工资大于平均工资的员工与部门号。并按部门排序,把上面的代码改成:
select count(*),t1.department_id
from employees t1,(select department_id,AVG(salary) avg_salary
from employees
group by department_id ) t2
where t1.department_id = t2.department_id and salary>t2.avg_salary
group by t1.department_id
order by department_id
得到最终结果: