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

二、放在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;