_11_ SQL 使用子查询

本文DEMO

https://gitee.com/changsanjiang/SQLDemo/tree/master

子查询

我们前面学习的, 所有SELECT语句都是简单查询, 即从单个数据库表中检索数据的单条语句.

**查询(Query)**
任何SQL语句都是查询. 但此术语一般指`SELECT语句`

SQL 还允许创建子查询(subquery), 即嵌套在其他查询中的查询. 为什么要这么做呢? 我们先看几个例子.

---

利用子查询进行过滤

先看一下本篇示例的关系表.

• Orders 表(订单)

列	说明
order_num	唯一的订单号
order_date	订单日期
cust_id	该订单的顾客ID

每个订单, 对应一个或多个物品, 订单细节存储在OrderItems表中
另外, cust_id关联到Customers表中的顾客ID.

• OrderItems 表(每个订单的实际物品, 每个物品一行. 对于Orders表中的每行, 在此表中有一行或多行物品)

列	说明
order_num	订单号
order_item	订单物品号(订单内的顺序)
prod_id	产品ID
quantity	物品数量
item_price	物品价格

此表主键为order_num和order_item

• Customers(顾客信息)

列	说明
cust_id	唯一的顾客ID
cust_name	顾客名
cust_address	地址
cust_city	城市
cust_state	州
cust_zip	邮政编码
cust_country	国家
cust_contact	联系名
cust_email	邮箱地址

了解以上表结构后, 现在, 我们假如需要列出购买prod_id = 'RGAN01'的所有顾客name和email, 应该怎么检索呢? 下面是具体步骤:

1. 检索出prod_id = 'RGAN01'的所有订单编号(order_num)
2. 通过order_num列出所有顾客的cust_id
3. 通过cust_id列出所有顾客的name和email

我们先实现第一步操作:

SELECT order_num 
FROM OrderItems
WHERE prod_id = 'RGAN01';

第二步:

SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num 
                    FROM Orders
                    WHERE prod_id = 'RGAN01');

第三步:

SELECT cust_name, cust_email
FROM Customers
WHERE cust_id IN (SELECT cust_id
                  FROM Orders
                  WHERE order_num IN (SELECT order_num 
                                      FROM OrderItems
                                      WHERE prod_id = 'RGAN01'));

输出:

image.png

分析:

由上, DBMS必须执行三条SELECT语句, 最里面的子查询返回相应列表, 此列表用于其外面的子查询的WHERE子句.

对于嵌套子查询的数目没有限制, 不过在实际使用时由于性能的限制, 不能嵌套太多的子查询.

注意, 作为子查询的SELECT语句只能查询单个列.

使用子查询并不总是最有效的方法, 关于更多操作, 后面的文章将会再次给出这个例子.

---

计算字段使用子查询

假如我们需要显示Customer表中每个顾客的订单总数.

由表关系的示例中, 可以看到 订单与相应的顾客ID存储在Orders表中.

执行这个操作, 要遵循下面的步骤:

1. 从Customer表中检索顾客列表
2. 对于检索的每个顾客, 统计其在Orders表中的订单数目

输入:

SELECT cust_name, cust_state, (SELECT COUNT(*) 
                               FROM Orders 
                               WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Custormers
ORDER BY cust_name;

输出:

image.png

分析:

orders是一个计算字段, 它是由圆括号中的子查询建立的. 该子查询对检索出的每个顾客执行一次, 在此列表中, 该子查询执行了5次, 因为检索出了5个顾客.