子查询做法
例:查询和 Zlotkey 相同部门的员工姓名和工资
思路:做这道题的时候,我们可以先查询部门和里的Zlotkey
SELECT department_id FROM employees WHERE last_name='ZlotKey'
然后在外层再查找员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE department_id=(SELECT department_id FROM employees WHERE last_name='ZlotKey')
例2:查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
思路:先查询平均工资最高的
SELECT VAG(salary)
FROM employees
然后再查询员工号和姓名工资
SELECT employee_id,last_name,salary
FROM employees
WHERE salary>(
SELECT AVG(salary)
FROM employees
)
例3:. 查询工资最高的员工的姓名,要求 first_name 和 last_name 显示为一列,列名为 姓.名
思路:先查询工资最高的
SELECT MAX(salary)
FROM employees
在将姓名拼接起来改为“姓.名”
SELECT CONCAT(first_name,last_name) AS '姓.名'
FROM employees
WHERE salary=(
SELECT MAX(salary)
FROM employees
)