SQL36 从 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) 检索)。

示例结果

返回产品名称 prod_name 和产品售出数量总和

prod_name quant_sold
egg 105
sockets 1300
coffee 1121
cola 34

示例解析

prod_name 是 cola 的 prod_id 为 a0003,quantity 总量为 34,返回结果无需排序。

示例

DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`prod_id` VARCHAR(255) NOT NULL COMMENT '产品 ID',
`prod_name` VARCHAR(255) NOT NULL COMMENT '产品名称'
);
INSERT INTO `Products` VALUES ('a0001','egg'),
('a0002','sockets'),
('a0013','coffee'),
('a0003','cola');

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

解答

解法类似于 35 题,同样主要有两种方式,第一种是通过内连接的方式。

SELECT prod_name, SUM(quantity) AS quant_sold FROM OrderItems, Products WHERE Products.prod_id = OrderItems.prod_id GROUP BY prod_name;

第二种则是通过子查询的方式。

SELECT prod_name, (SELECT SUM(quantity) FROM OrderItems WHERE OrderItems.prod_id = Products.prod_id) FROM Products;

SQL37 返回顾客名称和相关订单号

描述

Customers 表有字段顾客名称 cust_name、顾客 id cust_id

cust_id cust_name
cust10 andy
cust1 ben
cust2 tony
cust22 tom
cust221 an
cust2217 hex

Orders订单信息表,含有字段order_num订单号、cust_id顾客id

order_num cust_id
a1 cust10
a2 cust1
a3 cust2
a4 cust22
a5 cust221
a7 cust2217

问题

编写 SQL 语句,返回 Customers 表中的顾客名称(cust_name)和Orders 表中的相关订单号(order_num),并按顾客名称再按订单号对结果进行升序排序。你可以尝试用两个不同的写法,一个使用简单的等联结语法,另外一个使用 INNER JOIN。

示例结果

cust_name 代表用户名称 cust_name 和订单号 order_num。

cust_name order_num
an a5
andy a1
ben a2
hex a7
tom a4
tony a3

示例解析

顾客名称为 an 的 cust_id 为 cust221,他的订单号为 a5。

示例

DROP TABLE IF EXISTS `Orders`;
CREATE TABLE IF NOT EXISTS `Orders`(
order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
cust_id VARCHAR(255) NOT NULL COMMENT '顾客id'
);
INSERT `Orders` VALUES ('a1','cust10'),('a2','cust1'),('a3','cust2'),('a4','cust22'),('a5','cust221'),('a7','cust2217');

DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
cust_id VARCHAR(255) NOT NULL COMMENT '客户id',
cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名'
);
INSERT `Customers` VALUES ('cust10','andy'),('cust1','ben'),('cust2','tony'),('cust22','tom'),('cust221','an'),('cust2217','hex');

解答

考察 SQL 中的 INNER JOIN,其实也就是 JOIN。主要用于筛选出两个表中的交集部分。

使用语法如下:

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

因此,此题使用 INNER JOIN 实现的方法如下。

SELECT cust_name, Orders.order_num FROM Customers INNER JOIN Orders ON Orders.cust_id = Customers.cust_id ORDER BY cust_name;

此外,也可以使用最常用的 WHERE 来进行联接。

SELECT cust_name, order_num FROM Customers, Orders WHERE Customers.cust_id = Orders.cust_id ORDER BY cust_name;

SQL38 返回顾客名称和相关订单号以及每个订单的总价

描述

Customers 表有字段,顾客名称:cust_name、顾客 id:cust_id

cust_id cust_name
cust10 andy
cust1 ben
cust2 tony
cust22 tom
cust221 an
cust2217 hex

Orders 订单信息表,含有字段,订单号:order_num、顾客 id:cust_id

order_num cust_id
a1 cust10
a2 cust1
a3 cust2
a4 cust22
a5 cust221
a7 cust2217

OrderItems 表有字段,商品订单号:order_num、商品数量:quantity、商品价格:item_price

order_num quantity item_price
a1 1000 10
a2 200 10
a3 10 15
a4 25 50
a5 15 25
a7 7 7

问题

除了返回顾客名称和订单号,返回 Customers 表中的顾客名称(cust_name)和Orders 表中的相关订单号(order_num),添加第三列 OrderTotal,其中包含每个订单的总价,并按顾客名称再按订单号对结果进行升序排序。

示例结果

返回顾客名称 cust_name、订单号 order_num、订单总额 OrderTotal

cust_name order_num OrderTotal
an a5 375
andy a1 10000
ben a2 2000
hex a7 49
tom a4 1250
tony a3 150

示例解析

例如顾客名称 cust_name 为 an 的顾客的订单 a5 的订单总额为 quantity*item_price = 15 * 25 = 375,最后以 cust_name 和 order_num 来进行升序排序。

示例

DROP TABLE IF EXISTS `Orders`;
CREATE TABLE IF NOT EXISTS `Orders`(
order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
cust_id VARCHAR(255) NOT NULL COMMENT '顾客id'
);
INSERT `Orders` VALUES ('a1','cust10'),('a2','cust1'),('a3','cust2'),('a4','cust22'),('a5','cust221'),('a7','cust2217');

DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
cust_id VARCHAR(255) NOT NULL COMMENT '客户id',
cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名'
);
INSERT `Customers` VALUES ('cust10','andy'),('cust1','ben'),('cust2','tony'),('cust22','tom'),('cust221','an'),('cust2217','hex');

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
quantity INT(16) NOT NULL COMMENT '商品数量',
item_price INT(16) NOT NULL COMMENT '商品价格'
);
INSERT `OrderItems` VALUES ('a1',1000,10),('a2',200,10),('a3',10,15),('a4',25,50),('a5',15,25),('a7',7,7);

解答

SQL 语句先后顺序:

SELECT  ……
FROM ……
WHERE ……
GROUP BY ……
ORDER BY ……

书写 SQL 语句时,一定要遵守以上关键字的先后顺序。然后根据题意将各个条件组合即可。

SELECT cust_name, Orders.order_num, SUM(quantity * item_price) AS OrderTotal
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id AND Orders.order_num = OrderItems.order_num
GROUP BY Customers.cust_name, Orders.order_num
ORDER BY cust_name, Orders.order_num;

SQL39 确定哪些订单购买了 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),按订购日期对结果进行升序排序。

提示:这一次使用联结和简单的等联结语法。

示例结果

返回顾客 id cust_id 和定单日期 order_date

cust_id order_date
cust10 2022-01-01 00:00:00
cust1 2022-01-01 00:01:00

示例解析

产品 id 为 BR01 的订单 a0001 和 a002 的下单顾客 cust10 和 cust1 的下单时间分别为 2022-01-01 00:00:00 和 2022-01-01 00:01:00

示例

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
prod_id VARCHAR(255) NOT NULL COMMENT '产品id',
order_num VARCHAR(255) NOT NULL COMMENT '商品订单号'
);
INSERT `OrderItems` VALUES ('BR01','a0001'),('BR01','a0002'),('BR02','a0003'),('BR02','a0013');

DROP TABLE IF EXISTS `Orders`;
CREATE TABLE IF NOT EXISTS `Orders`(
order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
cust_id VARCHAR(255) NOT NULL COMMENT '顾客id',
order_date TIMESTAMP NOT NULL COMMENT '下单时间'
);
INSERT `Orders` VALUES ('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');

解答

多个条件的组合查询,先查询出产品 id 为 BR01order_num,然后从查询出的结果列中再去筛选出 Orders 表中 order_num,最后则是正序排序即可。

SELECT cust_id, order_date 
FROM Orders, (SELECT order_num FROM OrderItems WHERE prod_id = 'BR01') AS result
WHERE result.order_num = Orders.order_num
ORDER BY order_date;

SQL40 返回购买 prod_id 为 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,但是必须使用 INNER JOIN 语法。

示例结果

返回顾客 email cust_email

cust_email
cust10@cust.com
cust1@cust.com

示例解析

产品 id 为 BR01 的订单 a0001 和 a002 的下单顾客 cust10 和 cust1 的顾客 email cust_email 分别是:cust10@cust.com 、cust1@cust.com

示例

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
prod_id VARCHAR(255) NOT NULL COMMENT '产品id',
order_num VARCHAR(255) NOT NULL COMMENT '商品订单号'
);
INSERT `OrderItems` VALUES ('BR01','a0001'),('BR01','a0002'),('BR02','a0003'),('BR02','a0013');

DROP TABLE IF EXISTS `Orders`;
CREATE TABLE IF NOT EXISTS `Orders`(
order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
cust_id VARCHAR(255) NOT NULL COMMENT '顾客id',
order_date TIMESTAMP NOT NULL COMMENT '下单时间'
);
INSERT `Orders` VALUES ('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');

DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
cust_id VARCHAR(255) NOT NULL COMMENT '顾客id',
cust_email VARCHAR(255) NOT NULL COMMENT '顾客email'
);
INSERT `Customers` VALUES ('cust10','cust10@cust.com'),('cust1','cust1@cust.com'),('cust2','cust2@cust.com');

解答

多个内联接的组合,先是筛选出 OrderItemsOrders 表中 order_num 相同的列,然后根据筛选出的结果列中的 cust_idCustomers 表中找对应的顾客信息即可。

SELECT  
cust_email
FROM
Customers JOIN Orders ON
Orders.cust_id = Customers.cust_id
JOIN OrderItems ON OrderItems.prod_id = 'BR01' AND OrderItems.order_num = Orders.order_num;

致谢

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

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