sql学习分享--使用子查询

---

前言

select语句是sql的查询。我们迄今为止所看到的所有select 语句都是简单查询，即从单个数据库表中检索数据的单条语句。sql可以允许创建子查询，即嵌套在其他查询中的查询。今天一起分享下类似于这样的案例。

---

一、利用子查询进行过滤

目前已经创建好的表都是关系表。订单表存储在两个表中。每个订单包含订单编号、客户ID、订单日期，在Orders表中存储一行。各个订单的物品存储在相关的OrderItems表中。Orders表不存储顾客信息，只存储顾客ID。顾客的实际信息存储在Customers表中。（后面我会把用到的所有表名以及表结构整理出来和大家分享）

现在假如需要列出订购物品产品id为1的所有顾客，应该怎样检索？下面给出具体的步骤：

1. 检索包含物品产品id为1的所有的订单号码。
2. 检索具有前一步骤列出的订单编号的所有顾客的id。
3. 检索前一步骤返回的所有顾客ID的顾客信息。
   上述每个步骤都可以单独作为一个查询来执行。可以把一条select语句返回的结果用于另一条select语句的where子句。
   也可以使用子查询来把3个查询组合成一条语句。
   第一条select语句的含义很清晰，它对prod_id为1的所有订单物品，检索其order_num列。输出列出了三个包含此物品的订单号。

输入：

SELECT order_num from orderitems WHERE prod_id  ='1'

输出：

现在，我们知道了哪个订单包含要检索的物品，下一步查询与订单20220201、20220204、20220205相关的顾客id。利用in语句，编写如下的语句：
输入：

select cust_id from orders where order_num in (20220201,20220204,20220205)

输出：

现在，结合这两个查询，把第一个查询（返回订单号的哪一个）变为子查询，请看下面的select语句：
输入：

select cust_id from orders where order_num in (select order_num from orderitems where prod_id  ='1')

输出：

分析：

在select语句中，子查询总是从内向外处理。在处理上面的select语句时，它会执行下面的查询： SELECT order_num
from orderitems WHERE prod_id =‘1’;
此查询返回三个订单号：20220201,20220204,20220205。然后这三个值以in操作符要求的逗号分割的格式给外部查询的where子句，外部查询就相当于变成了select
cust_id from orders where order_num in
(20220201,20220204,20220205)。可以看到输出是对的，和前面没嵌套的查询结果是一样的

现在得到了订购物品产品id为1的所有顾客的id。下一步就是检索这些顾客id的顾客信息。检索的语句如下：
输入：

SELECT cust_name,cust_contact from Customers where cust_id in (1000,1001,1002)

输出：

可以把其中的where子句转换为子查询，而不是硬编码这些顾客id；
输入：

SELECT
	cust_name,
	cust_contact 
FROM
	Customers 
WHERE
	cust_id IN (
	SELECT
		cust_id 
	FROM
		orders 
	WHERE
	order_num IN ( SELECT order_num FROM orderitems WHERE prod_id = '1' ))

输出：

分析：
为了执行上述select语句，DBMS实际上必须执行三条select语句。最里面的子查询返回单号列表，此列表用于外面的子查询的where子句。外面的子查询返回顾客id列表，此顾客id列表用于最外层查询的where子句。最外层查询返回需要的数据。

可见，在where子句中使用子查询能够编写出功能很强且很灵活的sql语句；对于能嵌套的子查询的子查询的数量没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询。

二、作为计算字段使用子查询

使用子查询的另一方法是创建计算字段。假如需要显示Customers表中每个顾客的订单总数。订单与相应的顾客id存储在Orders表中。
执行这个操作，要遵循以下步骤：

1. 从Customers表中检索顾客列表；
2. 对于检索出的每个顾客，统计其在Orders表中的订单数量。

可以先使用select count（*）对表中的数据进行计数，并且通过提供一条where子句来过滤某个特定的顾客id，仅对该顾客的订单进行计数。例如，下列代码：

输入：

select count(*) as orders from orders where cust_id= '1001'

输出：

要对每个顾客执行count（*），应该将它作为一个子查询，如下：
输入：

SELECT cust_name, cust_state,( SELECT count(*) FROM orders WHERE orders.cust_id = customers.cust_id ) AS orders 
FROM
	customers 
ORDER BY
	cust_name

输出：

这条select语句对customers表中每个顾客返回三列信息：cust_name,cust_state和orders。orders是一个计算字段，它是由圆括号中的子查询建立的。该子查询对检索出的每个顾客执行一次。在这个案例中，该子查询一共查询了4次，因为检索出四个顾客。

子查询中的where子句与前面使用的where子句稍有不同，因为它使用了完全限定名，而不是只有列名（cust_id）。他指定表名和列名。下面的where子句告诉sql，比较Orders表中的cust_id和当前正从CusTomers表中检索的cust_id:WHERE orders.cust_id = customers.cust_id;

用一个点分割表明和列名，在有可能混淆列名时必须使用这种语法。在这个例子中，有两个cust_id列：一个在Customers表中，另一个在Orders中。如果不采用完全限定列名，DBMS会认为要对Orders表中的cust_id自身进行比较。因为 SELECT count() FROM orders WHERE orders.cust_id = customers.cust_id
总是返回Orders表中的订单数，但是这个结果不是我们想要的，例如下：

输入：

SELECT cust_name, cust_state,( SELECT count(*) FROM orders WHERE cust_id = cust_id ) AS orders 
FROM
	customers 
ORDER BY
	cust_name

输出：

虽然子查询在构造这种select语句时极有用，但必须注意限制有歧义的列。当然所有的解决方案都不只有着一种方式，解决方案还是有很多方式的。

---

和方法。