7、Oracle中的子查询

标签：salary employees 查询 department Oracle id SELECT

最近项目要用到Oracle，奈何之前没有使用过，所以在B站上面找了一个学习视频，用于记录学习过程以及自己的思考。
视频链接：
【尚硅谷】Oracle数据库全套教程，oracle从安装到实战应用
如果有侵权，请联系删除，谢谢。

学习目标：

描述子查询可以解决的问题。
定义子查询。
列出子查询的类型。
书写单行子查询和多行子查询。

1、子查询简介

1.1、使用子查询解决问题

谁的工资比 Abel 高?

仅适用一个查询语句难于解决需求或者不能解决需求时，可以考虑使用子查询。

1.2、子查询语法

SELECT	select_list
FROM	table
WHERE	expr operator
		 	(SELECT	select_list
		        FROM		table);

子查询 (内查询) 在主查询之前一次执行完成。
子查询的结果被主查询(外查询)使用。

SELECT last_name
FROM   employees
WHERE  salary >
               (SELECT salary
                FROM   employees
                WHERE  last_name = 'Abel');

注意事项：

子查询要包含在括号内。

将子查询放在比较条件的右侧。

行操作符对应单行子查询，多行操作符对应多行子查询。

1.3、子查询类型

单行子查询

多行子查询

1.4、单行子查询

只返回一行。
使用单行比较操作符。

执行单行子查询

题目：返回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);

1.5、在子查询中使用组函数

题目：返回公司工资最少的员工的last_name,job_id和salary

SELECT last_name, job_id, salary
FROM   employees
WHERE  salary = 
                (SELECT MIN(salary)
                 FROM   employees);

1.6、子查询中的 HAVING 子句

首先执行子查询。
向主查询中的HAVING 子句返回结果。

题目：查询最低工资大于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、子查询中常见的问题

2.1、非法使用子查询

SELECT employee_id, last_name
FROM   employees
WHERE  salary =
                (SELECT   MIN(salary)
                 FROM     employees
                 GROUP BY department_id);

执行结果：

ERROR at line 4:
ORA-01427: single-row subquery returns more thanone row

多行子查询使用单行比较符

2.2、子查询中的空值问题

SELECT last_name, job_id
FROM   employees
WHERE  job_id =
                (SELECT job_id
                 FROM   employees
                 WHERE  last_name = 'Haas');

执行结果：

no rows selected

子查询不返回任何行

3、多行子查询

返回多行。
使用多行比较操作符。

＞会any和all的区别

3.1、在多行子查询中使用 ANY 操作符

题目：返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary

SELECT employee_id, last_name, job_id, salary
FROM   employees
WHERE  salary < ANY
                    (SELECT salary
                     FROM   employees
                     WHERE  job_id = 'IT_PROG')
AND    job_id <> 'IT_PROG';

3.2、在多行子查询中使用 ALL 操作符

题目：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary

本质上就是工资比job_id为‘IT_PROG’部门最低工资还低的数据

SELECT employee_id, last_name, job_id, salary
FROM   employees
WHERE  salary < ALL
                    (SELECT salary
                     FROM   employees
                     WHERE  job_id = 'IT_PROG')
AND    job_id <> 'IT_PROG';

3.3、子查询中的空值问题

SELECT emp.last_name
FROM   employees emp
WHERE  emp.employee_id NOT IN
                             (SELECT mgr.manager_id
                              FROM   employees mgr);

执行结果：
no rows selected

4、课后习题

查询工资最低的员工信息: last_name, salary

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

查询平均工资最低的部门信息

		SELECT *
		FROM departments
		WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id 
			HAVING avg(salary) = (
				SELECT min(avg(salary))
				FROM employees
				GROUP BY department_id
			) 
		)

查询平均工资最低的部门信息和该部门的平均工资

select d.*, (select avg(salary) from employees where department_id = d.department_id)
from departments d
where d.department_id = (
      SELECT department_id
      FROM employees
      GROUP BY department_id 
      HAVING avg(salary) = (
			 SELECT min(avg(salary))
			 FROM employees
			 GROUP BY department_id
			  ) 
      )

查询平均工资最高的 job 信息


	1). 按 job_id 分组, 查询最高的平均工资	
	SELECT max(avg(salary))
	FROM employees
	GROUP BY job_id
	
	2). 查询出平均工资等于 1) 的 job_id
	SELECT job_id
	FROM employees
	GROUP BY job_id
	HAVING avg(salary) = (
		SELECT max(avg(salary))
		FROM employees
		GROUP BY job_id
	)
	
	3). 查询出 2) 对应的 job 信息
	SELECT *
	FROM jobs
	WHERE job_id = (
		SELECT job_id
		FROM employees
		GROUP BY job_id
		HAVING avg(salary) = (
			SELECT max(avg(salary))
			FROM employees
			GROUP BY job_id
		)
	)

查询平均工资高于公司平均工资的部门有哪些?

	1). 查询出公司的平均工资
	SELECT avg(salary)
	FROM employees
	
	2). 查询平均工资高于 1) 的部门 ID
	SELECT department_id
	FROM employees
	GROUP BY department_id
	HAVING avg(salary) > (
		SELECT avg(salary)
		FROM employees
	)

查询出公司中所有 manager 的详细信息.

	1). 查询出所有的 manager_id
	SELECT distinct manager_id
	FROM employeess
	
	2). 查询出 employee_id 为 1) 查询结果的那些员工的信息
	SELECT employee_id, last_name
	FROM employees
	WHERE employee_id in (
		SELECT distinct manager_id
		FROM employees
	)

各个部门中最高工资中最低的那个部门的最低工资是多少

	1). 查询出各个部门的最高工资
	SELECT max(salary)
	FROM employees
	GROUP BY department_id
	
	2). 查询出 1) 对应的查询结果的最低值: 各个部门中最低的最高工资(无法查询对应的 department_id)
	SELECT min(max(salary))
	FROM employees
	GROUP BY department_id
	
	3). 查询出 2) 所对应的部门 id 是多少: 各个部门中最高工资等于 2) 的那个部门的 id
	SELECT department_id
	FROM employees
	GROUP BY department_id 
	HAVING max(salary) = (
		SELECT min(max(salary))
		FROM employees
		GROUP BY department_id
	)
	
	4). 查询出 3) 所在部门的最低工资
	SELECT min(salary)
	FROM employees
	WHERE department_id = (
		SELECT department_id
		FROM employees
		GROUP BY department_id 
		HAVING max(salary) = (
			SELECT min(max(salary))
			FROM employees
			GROUP BY department_id
		)	
	)

查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

	1). 各个部门中, 查询平均工资最高的平均工资是多少
	SELECT max(avg(salary))
	FROM employees
	GROUP BY department_id
	
	
	2). 各个部门中, 平均工资等于 1) 的那个部门的部门号是多少
	SELECT department_id
	FROM employees
	GROUP BY department_id
	HAVING avg(salary) = (
		SELECT max(avg(salary))
		FROM employees
		GROUP BY department_id
	)
	
	
	
	3). 查询出 2) 对应的部门的 manager_id
	SELECT manager_id
	FROM departments
	WHERE department_id = (
		SELECT department_id
		FROM employees
		GROUP BY department_id
		HAVING avg(salary) = (
			SELECT max(avg(salary))
			FROM employees
			GROUP BY department_id
		)	
	)
	
	
	4). 查询出 employee_id 为 3) 查询的 manager_id 的员工的 last_name, department_id, email, salary
	SELECT last_name, department_id, email, salary
	FROM employees
	WHERE employee_id = (
		SELECT manager_id
		FROM departments
		WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING avg(salary) = (
				SELECT max(avg(salary))
				FROM employees
				GROUP BY department_id
			)	
		)	
	)

查询 1999 年来公司的人所有员工的最高工资的那个员工的信息.

		1). 查询出 1999 年来公司的所有的员工的 salary
		SELECT salary
		FROM employees
		WHERE to_char(hire_date, 'yyyy') = '1999'
		
		2). 查询出 1) 对应的结果的最大值
		SELECT max(salary)
		FROM employees
		WHERE to_char(hire_date, 'yyyy') = '1999'
		
		3). 查询工资等于 2) 对应的结果且 1999 年入职的员工信息		
		SELECT *
		FROM employees
		WHERE to_char(hire_date, 'yyyy') = '1999' AND salary = (
			SELECT max(salary)
			FROM employees
			WHERE to_char(hire_date, 'yyyy') = '1999'
		)

多行子查询的 any 和 all

		select department_id
		from employees
		group by department_id
		having avg(salary) >= any(
		                          --所有部门的平均工资
		                          select avg(salary)
		                          from employees
		                          group by department_id
		                       )
		
any 和任意一个值比较, 所以其条件最为宽松, 所以实际上只需和平均工资最低的比较, 返回所有值
而 all 是和全部的值比较, 条件最为苛刻, 所以实际上返回的只需和平均工资最高的比较, 所以返回
平均工资最高的 department_id

标签：salary,employees,查询,department,Oracle,id,SELECT
From： https://www.cnblogs.com/huageyiyangdewo/p/18250609