【SQL基础】联表查询、UNION（组合查询）题目

详细的联表查询内容见：链接

（1）

Products 表

prod_id	prod_name
a0001	egg
a0002	sockets
a0013	coffee
a0003	cola
a0023	soda

OrderItems 表

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

【问题】使用JOIN 联结 Products 表和 OrderItems 表，返回产品名称（prod_name）和每一项产品的总订单数（不是订单号），并按产品名称升序排序。

SELECT prod_name, COUNT(order_num) as totalnum
FROM Products
LEFT JOIN OrderItems
USING(prod_id)
GROUP BY prod_id
ORDER BY prod_name

COUNT(order_num)表示总量，同时要注意对于聚合函数，要使用group by 来进行分组，以便SQL引擎可以分辨出如何划分。LEFT JOIN是因为订单总数可能为0，但即便为0也要进行显示，因此用左连接OrderItem表，使得Products表里的表项即便没有被售卖，也可以得到计算。

（2）

Vendors表

vend_id
a0002
a0013
a0003
a0010

Products表

vend_id	prod_id
a0002	prod_id_iphone
a0003	prod_id_vivo phone
a0010	prod_id_huawei phone

【问题】列出供应商（Vendors 表中的 vend_id）及其可供产品的数量，包括没有产品的供应商。使用 OUTER JOIN 和 COUNT()聚合函数来计算 Products 表中每种产品的数量，最后根据 vend_id 升序排序。

SELECT v.vend_id, COUNT(prod_id) as totalnum
FROM Vendors v
LEFT JOIN Products
USING (vend_id)
GROUP BY v.vend_id
ORDER BY v.vend_id

同理，LEFT JOIN是用来包含products表的内容的。

组合查询

基本语法

UNION 运算符将两个或更多查询的结果组合起来，并生成一个结果集，其中包含来自 UNION 中参与查询的提取行。

UNION 基本规则：所有查询的列数和列顺序必须相同。每个查询中涉及表的列的数据类型必须相同或兼容。通常返回的列名取自第一个查询。

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

JOIN vs UNION：JOIN 中连接表的列可能不同，但在 UNION 中，所有查询的列数和列顺序必须相同。UNION 将查询之后的行放在一起（垂直放置），但 JOIN 将查询之后的列放在一起（水平放置），即它构成一个笛卡尔积。

例题

（1）

OrderItems表

prod_id	quantity
a0001	105
a0002	100
a0002	200
a0013	1121
a0003	10
a0003	19
a0003	5
BNBG	10002

【问题】将两个 SELECT 语句结合起来，以便从 OrderItems 表中检索产品 id（prod_id）和 quantity。其中，一个 SELECT 语句过滤数量为 100 的行，另一个 SELECT 语句过滤 id 以 BNBG 开头的产品，最后按产品 id 对结果进行升序排序。

SELECT prod_id, quantity
FROM OrderItems
WHERE quantity=100
UNION
SELECT prod_id, quantity
FROM OrderItems
WHERE prod_id like "BNBG%"

(2)

【问题】将两个 SELECT 语句结合起来，以便从 OrderItems 表中检索产品 id（prod_id）和 quantity。其中，一个 SELECT 语句过滤数量为 100 的行，另一个 SELECT 语句过滤 id 以 BNBG 开头的产品，最后按产品 id 对结果进行升序排序。 注意：这次仅使用单个 SELECT 语句。

SELECT prod_id, quantity
FROM OrderItems
WHERE quantity = 100 OR prod_id LIKE 'BNBG%'

如果要求不用union就可以用or

（3）

Products表

prod_name
flower
rice
ring
umbrella

Customers表

cust_name
andy
ben
tony
tom
an
lee
hex

【问题】编写 SQL 语句，组合 Products 表中的产品名称（prod_name）和 Customers 表中的顾客名称（cust_name）并返回，然后按产品名称对结果进行升序排序。

# UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。
SELECT prod_name
FROM Products
UNION
SELECT cust_name
FROM Customers
ORDER BY prod_name