mysql subquery_mysql-8-subquery

#进阶8：子查询

/*

出现在其他语句中的select语句(嵌套)

分类：

按子查询出现的位置：

SELECT 后面

FROM 后面

WHERE 或 HAVING 后面

EXISTS后面

*/

USE myemployees;

#一、where 或 having 后面

#1、单行

#案例1:谁的工资比 Abel高

SELECT last_name, salary

FROM employees

WHERE salary > (

SELECT salary

FROM employees

WHERE last_name='Abel'

);

#案例2:返回job_id与141号员工相同，salary比143号员工多的员工 姓名、job_id和工资

SELECT last_name, job_id, salary

FROM employees

WHERE job_id = (

SELECT job_id

FROM employees

WHERE employee_id = 141

) AND salary > (

SELECT salary

FROM employees

WHERE employee_id = 143

);

#案例3:查询最低工资大于50号部门最低工资的部门id和其最低工资

SELECT department_id, MIN(salary)

FROM employees

GROUP BY department_id

HAVING MIN(salary) > (

SELECT MIN(salary)

FROM employees

WHERE department_id = 50

);

#2、多行

#IN/NOT IN, ANY/SOME, ALL

#案例4:查询location_id是1400或1700的部门中的所有员工姓名

SELECT last_name

FROM employees

WHERE department_id IN (

SELECT department_id

FROM departments

WHERE location_id IN (1400, 1700)

);

#案例5:查询其他工种中比job_id为IT_PROG工种的任一工资低的员工的员工号、姓名、job_id和salary

#比任一低，< max()

SELECT employee_id, last_name, salary

FROM employees

WHERE salary < ANY(

SELECT DISTINCT salary

FROM employees

WHERE job_id = 'IT_PROG'

) AND job_id != 'IT_PROG';

SELECT employee_id, last_name, salary

FROM employees

WHERE salary < (

SELECT MAX(salary)

FROM employees

WHERE job_id = 'IT_PROG'

) AND job_id != 'IT_PROG';

#案例6:查询其他工种中比job_id为IT_PROG工种的任意工资低的员工的员工号、姓名、job_id和salary

SELECT employee_id, last_name, salary

FROM employees

WHERE salary < ALL(

SELECT DISTINCT salary

FROM employees

WHERE job_id = 'IT_PROG'

) AND job_id != 'IT_PROG';

#3、多行多列

#案例7:查询员工编号最小并且工资最高的员工信息

#普通写法

SELECT *

FROM employees

WHERE employee_id = (

SELECT MIN(employee_id)

FROM employees

) AND salary = (

SELECT MAX(salary)

FROM employees

);

#多行多列的写法，要求每一列的操作符是一致的

SELECT *

FROM employees

WHERE (employee_id, salary) = (

SELECT MIN(employee_id), MAX(salary)

FROM employees

);

#二、select 后面

#案例8：查询每个部门的员工个数

SELECT d.department_id, (

SELECT count(*)

FROM employees as e

WHERE e.department_id = d.department_id

) as num

FROM departments as d;

#案例9：查询员工号=102的部门名

#强行外查询。。。

SELECT (

SELECT department_name

FROM departments as d

INNER JOIN employees e

ON d.department_id = e.department_id

WHERE e.employee_id = 102

) 部门名;

#三、from 后面

#案例10:查询每个部门的平均工资的工资等级

SELECT avg(salary), department_id

FROM employees

GROUP BY department_id;

# 子查询结果充当表，必须起别名

SELECT avg_dep.*, g.grade_level

FROM (

SELECT avg(salary) as ag, department_id

FROM employees

GROUP BY department_id

) as avg_dep

INNER JOIN job_grades as g

ON avg_dep.ag BETWEEN g.lowest_sal AND g.highest_sal;

#四、exists /not exists 后面

#bool类型，只关心有没有

#用的较少，能够用其他子查询代替

SELECT EXISTS(

SELECT employee_id

FROM employees

WHERE salary=30000

);

#案例11:查询有员工的部门名

SELECT department_name

FROM departments as d

WHERE exists(

SELECT *

FROM employees as e

WHERE d.department_id = e.department_id

);