# 案例:查询每个部门的员工个数 SELECT d.*,( SELECT COUNT(*) FROM `employees` e WHERE e.`department_id`=d.`department_id` ) 个数 FROM `departments` d;
# 案例:查询员工号=102的部门名 select ( select `department_name` from `departments` d inner join `employees` e on e.`department_id`=d.`department_id` where e.`employee_id`=102 ) 部门名;
先查询每个部门的平均工资
# ① 查询每个部门的平均工资 select avg(`salary`),`department_id` from `employees` e group by `department_id`
然后②连接①的结果集和job_grades表,筛选条件平均工资between lowest_sal and highest_sal
#②连接①的结果集和job_grades表,筛选条件平均工资between lowest_sal and highest_sal select ag_dep.*,g.`grade_level` from ( select avg(`salary`) ag,`department_id` from `employees` e group by `department_id` )ag_dep inner join `job_grades` g on ag_dep.ag between lowest_sal and highest_sal;