多行子查询 是子查询的多重嵌套,是查询语句的综合应用,可见,多行子查询运用熟练将是mysql水平上质的飞跃。下面举得几个例子:
#例1:返回location_id是1400或1700的部门中的所有员工姓名
#①查询location_id是1400或1700的部门编号
SELECT department_id FROM departments
WHERE location_id IN(1400,1700)
#②查询department_id满足①结果的员工姓名
SELECT last_name FROM employees
WHERE department_id IN(
SELECT department_id FROM departments
WHERE location_id IN(1400,1700)
)
#例2:返回其它部门中比job_id为‘IT_PROG’部门任意工资低的员工的员
# 工号、姓名、job_id 以及salary
#①查询job_id为‘IT_PROG’部门工资
SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'
#②返回其它部门中,工资<any ①的结果
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
SELECT MAX(salary)
FROM employees
WHERE job_id = 'IT_PROG'
)AND job_id<>'IT_PROG';
#例3:查询和Zlotkey相同部门的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE department_id=(
SELECT department_id
FROM employees
WHERE last_name='Zlotkey'
)
#例2.查询工资比公司平均工资高的员工的员工号,姓名和工资。
#①查询公司平均工资
SELECT AVG(salary) FROM employees
#② 查询工资>①的员工的员工号,姓名和工资。
SELECT employee_id,last_name,salary
FROM employees
WHERE salary>(
SELECT AVG(salary) FROM employees
)
通过这些例题,我也你呢个体会到内在逻辑的重要性。
MYSQL的进步就是要靠勤加练习~