SQL 必知必会 50 题（46 - 50）

SQL46 列出供应商及其可供产品的数量

描述

有 Vendors 表含有 vend_id 供应商id.

vend_id
a0002
a0013
a0003
a0010

有 Products 表含有供应商 id 和供应产品 id

vend_id	prod_id
a0001	egg
a0002	prod_id_iphone
a00113	prod_id_tea
a0003	prod_id_vivo phone
a0010	prod_id_huawei phone

问题

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

注意：vend_id 列会显示在多个表中，因此在每次引用它时都需要完全限定它。

示例结果

返回供应商 id 和对应供应商供应的产品的个数

vend_id	prod_id
a0002	1
a0013	0
a0003	1
a0010	1

示例解析

供应商 a00013 供应的商品不在 Products 表中所以为 0，其他供应商供应的产品为 1 个。

示例

DROP TABLE IF EXISTS `Vendors`;
CREATE TABLE IF NOT EXISTS `Vendors` (
  `vend_id` VARCHAR(255) NOT NULL COMMENT 'vend名称'
);
INSERT INTO `Vendors` VALUES ('a0002'),
('a0013'),
('a0003'),
('a0010');

DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`vend_id` VARCHAR(255) NOT NULL COMMENT '产品 ID',
`prod_id` VARCHAR(255) NOT NULL COMMENT '产品名称'
);
INSERT INTO `Products` VALUES ('a0001','egg'),
('a0002','prod_id_iphone'),
('a00113','prod_id_tea'),
('a0003','prod_id_vivo phone'),
('a0010','prod_id_huawei phone');

解答

利用 COUNT() 函数对 Vendors 中的 vend_id 分组并统计。

另外则是考察 LEFT JOIN，即题目中所说的 OUTER JOIN，其具体使用语法如下。

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

SELECT Vendors.vend_id, COUNT(Products.prod_id) FROM Vendors LEFT JOIN Products ON Vendors.vend_id = Products.vend_id GROUP BY Vendors.vend_id ORDER BY Vendors.vend_id;

SQL47 将两个 SELECT 语句结合起来（一）

描述

表 OrderItems 包含订单产品信息，字段 prod_id 代表产品 id、quantity 代表产品数量

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 对结果进行升序排序。

示例结果

返回产品 id prod_id和产品数量 quantity

prod_id	quantity
a0002	100
BNBG	10002

示例解析

产品 id a0002 因为数量等于 100 被选取返回；BNBG 因为是以 BNBG 开头的产品所以返回；最后以产品 id 进行排序返回。

示例

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	prod_id VARCHAR(255) NOT NULL COMMENT '产品id',
	quantity VARCHAR(255) NOT NULL COMMENT '商品数量'
);
INSERT `OrderItems` VALUES ('a0001',105),('a0002',100),('a0002',200),('a0013',1121),('a0003',10),('a0003',19),('a0003',5),('BNBG',10002);

解答

依照题意，其实我们利用一个 SELECT 然后搭配 WHERE 条件查询就能将满足题意的纪律筛选出来。但是为了练习关键字 UNION 的使用，可以将两个条件查询语句分开，然后用 UNION 联接起来，最后则是按照 prod_id 正序排列。

SELECT prod_id, quantity FROM OrderItems WHERE quantity = '100' UNION SELECT prod_id, quantity FROM OrderItems WHERE prod_id LIKE 'BNBG%' ORDER BY prod_id;

SQL48 将两个 SELECT 语句结合起来（二）

描述

表 OrderItems 包含订单产品信息，字段 prod_id 代表产品 id、quantity 代表产品数量。

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 语句。

示例结果

返回产品 id prod_id 和产品数量 quantity

prod_id	quantity
a0002	100
BNBG	10002

示例解析

产品 id a0002 因为数量等于 100 被选取返回；BNBG 因为是以 BNBG 开头的产品所以返回；最后以产品 id 进行排序返回。

示例

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	prod_id VARCHAR(255) NOT NULL COMMENT '产品id',
	quantity VARCHAR(255) NOT NULL COMMENT '商品数量'
);
INSERT `OrderItems` VALUES ('a0001',105),('a0002',100),('a0002',200),('a0013',1121),('a0003',10),('a0003',19),('a0003',5),('BNBG',10002);

解答

根据题意仅使用单个 SELECT 语句，那就直接利用 WHERE 条件查询，然后将两个条件利用连接符 OR 并联即可。

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

SQL49 组合 Products 表中的产品名称和 Customers 表中的顾客名称

描述

Products 表含有字段 prod_name 代表产品名称

prod_name
flower
rice
ring
umbrella

Customers 表代表顾客信息，cust_name 代表顾客名称

cust_name
andy
ben
tony
tom
an
lee
hex

问题

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

示例结果

prod_name
an
andy
ben
flower
hex
lee
rice
ring
tom
tony
umbrella

示例解析

拼接 cust_name 和 prod_name 并根据结果升序排序

示例

DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`prod_name` VARCHAR(255) NOT NULL COMMENT '产品名称'
);
INSERT INTO `Products` VALUES ('flower'),
('rice'),
('ring'),
('umbrella');

DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
	cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名'
);
INSERT `Customers` VALUES ('andy'),('ben'),('tony'),('tom'),('an'),('lee'),('hex');

解答

同样考察 UNION 操作符，通过将两个 SELECT 语句的结果合并起来即可。

这里需要注意的是得出的结果是 prod_name，所以需要注意两个 SELECT 语句的先后顺序，再然后就是需要对查询出的记录按照 prod_name 正序排列。

SELECT prod_name FROM Products UNION SELECT cust_name FROM Customers ORDER BY prod_name;

SQL50 纠错4

描述

表 Customers 含有字段 cust_name 顾客名、cust_contact 顾客联系方式、cust_state 顾客州、cust_email 顾客 email

cust_name	cust_contact	cust_state	cust_email
cust10	8695192	MI	cust10@cust.com
cust1	8695193	MI	cust1@cust.com
cust2	8695194	IL	cust2@cust.com

问题

修正下面错误的 SQL

SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state = 'MI' 
ORDER BY cust_name; 
UNION 
SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state = 'IL' ORDER BY cust_name;

示例结果

返回顾客名称：cust_name、顾客联系方式：cust_contact、顾客 email：cust_email

cust_name	cust_contact	cust_email
cust1	8695193	cust1@cust.com
cust10	8695192	cust10@cust.com
cust2	8695194	cust2@cust.com

示例解析

返回住在 “IL” 和 “MI” 的顾客信息，最后根据顾客名称升序排序。

示例

DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
    cust_name VARCHAR(255) NOT NULL COMMENT '顾客id',
    cust_contact VARCHAR(255) NOT NULL COMMENT '顾客联系方式',
    cust_state VARCHAR(255) NOT NULL COMMENT '顾客州',
    cust_email VARCHAR(255) NOT NULL COMMENT '顾客email'
  );
INSERT `Customers` VALUES ('cust10','8695192','MI','cust10@cust.com'),('cust1','8695193','MI','cust1@cust.com'),('cust2','8695194','IL','cust2@cust.com');

解答

主要有两处错误，第一是 UNION 前多加了 ;，; 表示一条 SQL 的结束，加在此处不适合。

第二则是只主要对 cust_name 排序一次即可，放在 UNION 前的 ORDER BY 关键字显然位置不对。

SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state = 'MI'  
UNION 
SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state = 'IL' ORDER BY cust_name;

致谢

感谢牛客网提供的题目列表。