（十四）MySQL语法-子查询(二)

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

二、放在select后面的子查询  仅仅支持标量子查询 案例1：查询每个部门的员工个数 select d.*,(select count(1) from employees e where e.department_id=d.department_id ) from departments d;--27行  有的部门是没有员工的显示为0 select d.department_id,count(1) from employees e,departments d where e.department_id=d.department_id group by d.department_id--11行  所有的部门都是有员工的 案例2：查询员工号码=102的部门名 select (select d.department_name from employees e INNER join departments d on e.department_id=d.department_id where e.employee_id='102') 部门名;   三、放在from后面的select语句 将查询结果充当一张表，必须起别名 案例1：查询每个部门的平均工资的工资等级 步骤一：查询每个部门的平均工资 select avg(salary),department_id from employees GROUP BY department_id; 步骤二：工资等级 select * from job_grades; 步骤三：整体结合 select ag_dep.*,g.grade_level from (select avg(salary) ag,department_id from employees GROUP BY department_id) ag_dep INNER JOIN job_grades g on ag_dep.ag BETWEEN lowest_sal and highest_sal;