Question

子查询是嵌套在较大查询中的 SQL 查询，也称内部查询或内部选择，包含子查询的语句也称为外部查询或外部选择。简单来说，子查询就是指将一个 SELECT 查询（子查询）的结果作为另一个 SQL 语句（主查询）的数据来源或者判断条件。

子查询可以嵌入 SELECT 、 INSERT 、 UPDATE 和 DELETE 语句中，也可以和 = 、 < 、 > 、 IN 、 BETWEEN 、 EXISTS 等运算符一起使用。

子查询常用在 WHERE 子句和 FROM 子句后边：

• 当用于 WHERE 子句时，根据不同的运算符，子查询可以返回单行单列、多行单列、单行多列数据。子查询就是要返回能够作为 WHERE 子句查询条件的值。
• 当用于 FROM 子句时，一般返回多行多列数据，相当于返回一张临时表，这样才符合 FROM 后面是表的规则。这种做法能够实现多表联合查询。

注意：MySQL 数据库从 4.1 版本才开始支持子查询，早期版本是不支持的。

用于 WHERE 子句的子查询的基本语法如下：

SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name operator
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])

• 子查询需要放在括号 ( ) 内。
• operator 表示用于 WHERE 子句的运算符，可以是比较运算符（如 = , < , > , <> 等）或逻辑运算符（如 IN , NOT IN , EXISTS , NOT EXISTS 等），具体根据需求来确定。

用于 FROM 子句的子查询的基本语法如下：

SELECT column_name [, column_name ]
FROM (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE]) AS temp_table_name [, ...]
[JOIN type JOIN table_name ON condition]
WHERE condition;

• 用于 FROM 的子查询返回的结果相当于一张临时表，所以需要使用 AS 关键字为该临时表起一个名字。
• 子查询需要放在括号 ( ) 内。
• 可以指定多个临时表名，并使用 JOIN 语句连接这些表。

返回购买价格为 10 美元或以上产品的顾客列表

OrderItems 表示订单商品表，含有字段订单号： order_num 、订单价格： item_price ； Orders 表代表订单信息表，含有顾客 id：cust_id 和订单号： order_num

OrderItems 表:

order_num	item_price
a1	10
a2	1
a2	1
a4	2
a5	5
a2	1
a7	7

Orders 表：

order_num	cust_id
a1	cust10
a2	cust1
a2	cust1
a4	cust2
a5	cust5
a2	cust1
a7	cust7

【问题】使用子查询，返回购买价格为 10 美元或以上产品的顾客列表，结果无需排序。

答案：

SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT DISTINCT order_num
    FROM OrderItems
    where item_price >= 10)

确定哪些订单购买了 prod_id 为 BR01 的产品（一）

表 OrderItems 代表订单商品信息表， prod_id 为产品 id； Orders 表代表订单表有 cust_id 代表顾客 id 和订单日期 order_date

OrderItems 表：

prod_id	order_num
BR01	a0001
BR01	a0002
BR02	a0003
BR02	a0013

Orders 表：

order_num	cust_id	order_date
a0001	cust10	2022-01-01 00:00:00
a0002	cust1	2022-01-01 00:01:00
a0003	cust1	2022-01-02 00:00:00
a0013	cust2	2022-01-01 00:20:00

【问题】

编写 SQL 语句，使用子查询来确定哪些订单（在 OrderItems 中）购买了 prod_id 为 "BR01" 的产品，然后从 Orders 表中返回每个产品对应的顾客 ID（ cust_id ）和订单日期（ order_date ），按订购日期对结果进行升序排序。

答案：

# 写法 1：子查询
SELECT cust_id,order_date
FROM Orders
WHERE order_num IN
    (SELECT order_num
     FROM OrderItems
     WHERE prod_id = 'BR01' )
ORDER BY order_date;

# 写法 2: 连接表
SELECT b.cust_id, b.order_date
FROM OrderItems a,Orders b
WHERE a.order_num = b.order_num AND a.prod_id = 'BR01'
ORDER BY order_date

返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（一）

你想知道订购 BR01 产品的日期，有表 OrderItems 代表订单商品信息表， prod_id 为产品 id； Orders 表代表订单表有 cust_id 代表顾客 id 和订单日期 order_date ； Customers 表含有 cust_email 顾客邮件和 cust_id 顾客 id

OrderItems 表：

prod_id	order_num
BR01	a0001
BR01	a0002
BR02	a0003
BR02	a0013

Orders 表：

order_num	cust_id	order_date
a0001	cust10	2022-01-01 00:00:00
a0002	cust1	2022-01-01 00:01:00
a0003	cust1	2022-01-02 00:00:00
a0013	cust2	2022-01-01 00:20:00

Customers 表代表顾客信息， cust_id 为顾客 id， cust_email 为顾客 email

cust_id	cust_email
cust10	cust10@cust.com
cust1	cust1@cust.com
cust2	cust2@cust.com

【问题】返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（ Customers 表中的 cust_email ），结果无需排序。

提示：这涉及 SELECT 语句，最内层的从 OrderItems 表返回 order_num ，中间的从 Customers 表返回 cust_id 。

答案：

# 写法 1：子查询
SELECT 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 = 'BR01'))

# 写法 2: 连接表（inner join）
SELECT c.cust_email
FROM OrderItems a,Orders b,Customers c
WHERE a.order_num = b.order_num AND b.cust_id = c.cust_id AND a.prod_id = 'BR01'

# 写法 3：连接表（left join）
SELECT c.cust_email
FROM Orders a LEFT JOIN
  OrderItems b ON a.order_num = b.order_num LEFT JOIN
  Customers c ON a.cust_id = c.cust_id
WHERE b.prod_id = 'BR01'

返回每个顾客不同订单的总金额

我们需要一个顾客 ID 列表，其中包含他们已订购的总金额。

OrderItems 表代表订单信息， OrderItems 表有订单号： order_num 和商品售出价格： item_price 、商品数量： quantity 。

order_num	item_price	quantity
a0001	10	105
a0002	1	1100
a0002	1	200
a0013	2	1121
a0003	5	10
a0003	1	19
a0003	7	5

Orders 表订单号： order_num 、顾客 id： cust_id

order_num	cust_id
a0001	cust10
a0002	cust1
a0003	cust1
a0013	cust2

【问题】

编写 SQL 语句，返回顾客 ID（ Orders 表中的 cust_id ），并使用子查询返回 total_ordered 以便返回每个顾客的订单总数，将结果按金额从大到小排序。

答案：

# 写法 1：子查询
SELECT o.cust_id AS cust_id, tb.total_ordered AS total_ordered
FROM (SELECT order_num, Sum(item_price * quantity) AS total_ordered
    FROM OrderItems
    GROUP BY order_num) AS tb,
  Orders o
WHERE tb.order_num = o.order_num
ORDER BY total_ordered DESC

# 写法 2：连接表
SELECT b.cust_id, Sum(a.quantity * a.item_price) AS total_ordered
FROM OrderItems a,Orders b
WHERE a.order_num = b.order_num
GROUP BY cust_id
ORDER BY total_ordered DESC

从 Products 表中检索所有的产品名称以及对应的销售总数

Products 表中检索所有的产品名称： prod_name 、产品 id： prod_id

prod_id	prod_name
a0001	egg
a0002	sockets
a0013	coffee
a0003	cola

OrderItems 代表订单商品表，订单产品： prod_id 、售出数量： quantity

prod_id	quantity
a0001	105
a0002	1100
a0002	200
a0013	1121
a0003	10
a0003	19
a0003	5

【问题】

编写 SQL 语句，从 Products 表中检索所有的产品名称（ prod_name ），以及名为 quant_sold 的计算列，其中包含所售产品的总数（在 OrderItems 表上使用子查询和 SUM(quantity) 检索）。

答案：

# 写法 1：子查询
SELECT p.prod_name, tb.quant_sold
FROM (SELECT prod_id, Sum(quantity) AS quant_sold
    FROM OrderItems
    GROUP BY prod_id) AS tb,
  Products p
WHERE tb.prod_id = p.prod_id

# 写法 2：连接表
SELECT p.prod_name, Sum(o.quantity) AS quant_sold
FROM Products p,
  OrderItems o
WHERE p.prod_id = o.prod_id
GROUP BY p.prod_name（这里不能用 p.prod_id，会报错）