将子查询结果充当一张表,要求必须起别名
案例:查询每个部门的平均工资的工资等级
1.查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;
2.连接1的结果集和job_grades表,筛选条件平均工资between lowest_sal and highest_sal
SELECT ag_dep.*,g.'grade_lever'
FROM(
SELECT AVG(salary),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