子查询

--子查询练习
/*
1、子查询（内查询）在主查询（外查询）之前一次执行完成
2、子查询的结果被主查询使用
3、在查询列表中使用子查询，只能是单行单列。见练习2
4、除非进行TOP N 分析，否则不要在子查询中使用ORDER BY语句，
因子查询效率较低，排序耗费资源。见练习3
*/

--练习1：查询所有工资大于CLARK的员工信息
--把子查询结果当做一个值使用
SELECT * FROM emp
WHERE sal > (SELECT sal FROM emp WHERE ename = 'CLARK')

--练习2：查询所有员工信息以及工资总和
--把子查询结果当做一个列使用
SELECT emp.*,(SELECT SUM(sal) FROM emp) 工资总和
FROM emp;

--练习3：查询工资最高TOP 5
--把子查询结果当做一个表使用
SELECT * FROM (SELECT * FROM emp ORDER BY sal DESC)
WHERE ROWNUM <= 5;

--练习4：查询员工表中第6到第12条数据
--利用子查询解决ROWNUM不能计算大于等于某个值的问题
SELECT * FROM (SELECT ROWNUM rnum,emp.* FROM emp)
WHERE rnum BETWEEN 6 AND 12;

--推荐写法（提前过滤）
SELECT * FROM (SELECT ROWNUM rnum, emp.* FROM emp WHERE ROWNUM<=12)
WHERE rnum >=6;

--多行子查询
--练习1：查询所有不是部门经理的员工
--IN 的使用
SELECT * FROM emp e
WHERE e.empno NOT IN (SELECT manager_id FROM DEPT
WHERE manager_id IS NOT NULL)

--练习2：查询所有员工人数不少于3人的部门信息
--子查询中可以使用子句
SELECT * FROM dept
WHERE deptno IN (SELECT deptno FROM emp
GROUP BY deptno
HAVING COUNT(*)>=3 )

--相关子查询（内外交互式）
--练习：查询员工编号，姓名，部门编号，工资，本部门工资总和
SELECT empno,ename,deptno,sal,
(SELECT SUM(sal) FROM emp
WHERE e.deptno = deptno) 部门工资总和
FROM emp e
ORDER BY deptno;

1.子查询

前面的系列介绍的都是简单的查询场景，其中都只涉及到单张表的数据检索。但是在日常是实际应用中，数据模型之间的关系都非常的复杂，数据的需求一般都是来源于多个数据模型之间的组合而成，即对应多张表的数据关联。

对应以上场景，在SQL中一般有三种实现的方式：

• 使用多个单条SQL，按逻辑步骤检索，将其中的一条检索结果作为下一条检索的条件；
• 使用子查询，即将多个单挑SQL利用相应的逻辑关键字合并，子查询是DBMS所支持；
• 使用表联结的方式，即join；

本章就简单的回顾下SQL中的子查询，从上面的总结中可以看出，子查询其实利用模型之间的关系将单条SQL合并成一条复杂的SQL。那么如果要写出这样的复杂子查询的SQL，首先需要梳理清楚需求中的数据模型之间的关系，根据需求的结果区分出查询主体，查询的关联关系体，然后再分成单步骤的SQL，最后将其合并即可；

下面看个例子：

有三个实体，对应三张表：

• 顾客表
• 商品表
• 订单表

订单中含有商品id，顾客id。现在需求：查询购买商品x1的所有顾客。

1. 分析：最终的结果是要查询出顾客，所以查询柱体是顾客，即外查询是顾客。但是查询顾客的条件是，购买了x1商品的顾客，所以需要查出购买了x1商品的顾客id，然后根据顾客id查询顾客，所以子查询是根据商品id x1查询。

2. 步骤：

• 在表orders中查询x1商品的顾客id： select cus_id from orders where mer_id = 'x1';

• 根据上述的查询结果作为条件，查询顾客：select * from customers where cus_id = 'xxxx';

最后将根据需求结果和实体的关联关系合并SQL：

select * from customers where customers.cus_id in (select cus_id from orders where mer_id = 'x1');

其实SQL中子查询有两种应用方式：

• 第一种也就上面的最常使用到的场景，将子查询的结果作为外查询的条件，即子查询属于外查询where子句的一部分
• 第二种常用是将子查询统计结果作为外查询的列

比如需求：统计每个顾客购订单数量。

1. 分析：顾客仍然是主体，所以外查询是查顾客表。但是执行的逻辑刚好和上述例子相反，上述是以商品id为条件查询顾客。这里是查询顾客以及其订单数，但是订单数的统计是从顾客这一维度出发，所以需要根据顾客查询订单数。

2. 步骤：

• 先查询出所有顾客： select * from customers;
• 再根据顾客去统计每个顾客的订单数: select count(*) from orders where orders.cus_id = 'xxxxx';

最后组合SQL：

select cus_id, cus_name, (select count(*) from orders where orders.cus_id = customers.cus_id) from customers;

以上例子是子查询的第二种用法。

2.总结

上述总结的子查询的两种方式：

• 要么是根据子查询的逻辑结果作为外查询的查询条件（子查询在where子句中）
• 要么是根据外查询的结果作为子查询的条件(子查询在select子句中)

SQL中没有限制子查询的数量，但是一般实际应用中子查询的不宜使用过多：

• 使用子查询必然会导致SQL更为复杂，SQL表述的语义较为难以理解，可阅读性变差
• 子查询使用过多，会严重消耗性能
• 子查询不利于SQL的调试，问题的排查