【大数据系列之MySQL】（二十七）：子查询的经典使用案例

案例1：查询工资最低的员工信息：last_name，salary（where后标量子查询）

首先查询出最低工资，然后使用该工资进行筛选

SELECT last_name, salary FROM employees WHERE salary =( SELECT min( salary ) FROM employees );

案例2：查询平均工资最低的部门信息（from后表子查询，where后标量子查询）

先查询出部门的平均工资，然后在查哪个部门的平均工资最低，再查哪个部门的平均工资等于最低工资，最后查询部门信息

SELECT
	* 
FROM
	departments 
WHERE
	department_id =(
	SELECT
		department_id 
	FROM
		( SELECT department_id, avg( salary ) avg FROM employees GROUP BY department_id ) ad 
	WHERE
		avg =(
		SELECT
			min( avg ) 
		FROM
		( SELECT department_id, avg( salary ) avg FROM employees GROUP BY department_id ) ad 
	));

案例3：查询平均工资最低的部门信息和该部门的平均工资

SELECT
	* 
FROM
	(
	SELECT
		* 
	FROM
		departments 
	WHERE
		department_id =(
		SELECT
			department_id 
		FROM
			( SELECT department_id, avg( salary ) avg FROM employees GROUP BY department_id ) ad 
		WHERE
			avg =(
			SELECT
				min( avg ) 
			FROM
				( SELECT department_id, avg( salary ) avg FROM employees GROUP BY department_id ) ad 
			))) a
	JOIN ( SELECT department_id, avg( salary ) avg FROM employees GROUP BY department_id ) b ON a.department_id = b.department_id;

案例4：查询平均工资最高的job信息

先查询出平均工资最高的job_id是什么，然后再去job表中查询

SELECT
	* 
FROM
	jobs 
WHERE
	job_id = (
	SELECT
		job_id 
	FROM
		employees 
	GROUP BY
		job_id 
	ORDER BY
	avg( salary ) DESC 
	LIMIT 1)

案例5：查询平均工资高于公司平均工资的部门有哪些？

首先查询出公司的平均工资，然后再查询出各部门的平均工资，然后使用having进行筛选

SELECT
	department_id,
	avg( salary ) ag 
FROM
	employees 
GROUP BY
	department_id 
HAVING
	ag >(
	SELECT
		avg( salary ) ag 
	FROM
	employees)

案例6：查询出公司中所有manager的详细信息

首先查询出公司中所有的manager的id，然后使用in去做遍历

SELECT
	* 
FROM
	employees 
WHERE
	employee_id IN (
	SELECT DISTINCT
		manager_id 
	FROM
		employees 
WHERE
	manager_id IS NOT NULL)

案例7：各个部门中最高工资中最低的那个部门的最低工资是多少

SELECT
	min( salary ) 
FROM
	employees 
WHERE
	department_id = ( SELECT department_id FROM employees GROUP BY department_id ORDER BY max( salary ) LIMIT 1 );

案例8：查询平均工资最高的部门的manager的详细信息：last_name，department_id，email，salary

SELECT
	last_name,
	department_id,
	email,
	salary 
FROM
	employees 
WHERE
	employee_id IN ( SELECT DISTINCT manager_id FROM employees WHERE manager_id IS NOT NULL ) 
	AND department_id = ( SELECT department_id FROM employees GROUP BY department_id ORDER BY avg( salary ) DESC LIMIT 1 );