查询员工表中工资大于本部门平均工资的员工last_name,salary,department_id
方式一: 相关子查询
SELECT last_name,salary,department_id
FROM employees e1
WHERE salary > (
SELECT AVG(salary)
FROM employees e2
WHERE department_id = e1.department_id
);
方式二: 在FROM中声明子查询,即将子查询的结果看做一张新的表
SELECT *
FROM employees e,(
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id) t_dept_avg_sal
WHERE e.department_id = t_dept_avg_sal.department_id
AND e.salary > t_dept_avg_sal.avg_sal
在第一节基础上 更新了函数和执行顺序:实操了字符数函数、时间函数、字段截取函数、interval函数等,除最后顺序实操因未导入数据库,导致无法执行