SQL练习：查询各个部门中工资高于本部门的平均工资的员工数和部门号，并按部门号排序

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

得到最终结果：