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;

致谢

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

关注公众号,获取最新文章更新
公众号