SQL练习:查询各个部门中工资高于本部门的平均工资的员工数和部门号,并按部门号排序
1.部门表结构如下:解题思路:查询各个部门中工资高于本部门的平均工资的员工数和部门号,并按部门号排序第一步:首先查询出部门的平均工资select department_id,AVG(salary)from employeesgroup by department_id查询结果:第二步:此时需要把第一步查询到的结果集作为新表去做连表查询,即:select * from a,b where a.dep
·
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
得到最终结果:
更多推荐
已为社区贡献1条内容
所有评论(0)