SQL31 纠错3

描述

OrderItems 表含有 order_num 订单号

order_num
a002
a002
a002
a004
a007

问题

将下面代码修改正确后执行

SELECT order_num, COUNT(*) AS items 
FROM OrderItems
GROUP BY items
HAVING COUNT(*) >= 3
ORDER BY items, order_num;

示例结果

返回订单号 order_num 和出现的次数 items

order_num items
a002 3

示例解析

由于订单号 a002 出现了三次,所以返回3

示例

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
order_num VARCHAR(255) NOT NULL COMMENT '商品订单号'
);
INSERT `OrderItems` VALUES ('a002'),('a002'),('a002'),('a004'),('a007');

解答

最主要的错误在于 GROUP BY 后边跟着的是统计结果,其次在于 HAVING 后边的 COUNT() 其实是可以不用再次计算的,可以直接利用已经统计出的结果。第二处不算错误,但是改了之后能提高 SQL 语句所执行的效率。

SELECT order_num, COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING COUNT(*) >= 3 ORDER BY items, order_num;
SELECT order_num, COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING items >= 3 ORDER BY items, order_num;

SQL32 返回购买价格为 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 美元或以上产品的顾客列表,结果无需排序。
注意:你需要使用 OrderItems 表查找匹配的订单号(order_num),然后使用Order 表检索这些匹配订单的顾客 ID(cust_id)。

示例结果

返回顾客 id cust_id

cust_id
cust10

示例解析

cust10 顾客下单的订单为 a1,a1 的售出价格大于等于 10

示例

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
item_price INT(16) NOT NULL COMMENT '售出价格'
);
INSERT `OrderItems` VALUES ('a1',10),('a2',1),('a2',1),('a4',2),('a5',5),('a2',1),('a7',7);

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'),('a2','cust1'),('a4','cust2'),('a5','cust5'),('a2','cust1'),('a7','cust7');

解答

题目已经提示使用子查询,则先用最简单的条件查询从 OrderItems 表中找出订单价格不低于 10 美元的订单,接着从筛选出的结果中再次筛选出对应订单的顾客 id,需要注意的一点是对顾客 id 去重,需要使用到关键字 DISTINCT

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

SQL33 确定哪些订单购买了 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');

解答

使用子查询,先从 OrderItems 表中查询出 prod_idBR01 的记录 ,然后再从 Orders 表中筛选出 order_num 为子查询结果集中的记录,最后按照 order_date 进行排序即可。主要是通过对条件查询语句的嵌套使用,从而实现多重筛选。

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

SQL34 返回购买 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。

示例结果

返回顾客 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');

解答

多重条件查询的过滤,只要细心一点,就能做出来。拆分为 3 个条件查询后,从内向外依次查询,然后基于上一层查询结果再做条件过滤。

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'));

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

描述

我们需要一个顾客 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 以便返回每个顾客的订单总数,将结果按金额从大到小排序。

提示:你之前已经使用 SUM() 计算订单总数。

示例结果

返回顾客 id cust_id 和 total_order 下单总额

cust_id total_ordered
cust2 2242
cust1 1300
cust10 1050
cust2 104

示例解析

cust2 在 Orders 里面的订单 a0013,a0013 的售出价格是 2 售出数量是 1121,总额是 2242,最后返回 cust2 的支付总额是 2242。

示例

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
item_price INT(16) NOT NULL COMMENT '售出价格',
quantity INT(16) NOT NULL COMMENT '商品数量'
);
INSERT `OrderItems` VALUES ('a0001',10,105),('a0002',1,1100),('a0002',1,200),('a0013',2,1121),('a0003',5,10),('a0003',1,19),('a0003',7,5);

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 ('a0001','cust10'),('a0003','cust1'),('a0013','cust2');

解答

题目要求利用子查询,则实现如下。

SELECT cust_id, (SELECT SUM(item_price * quantity) FROM OrderItems WHERE OrderItems.order_num = Orders.order_num) AS total_ordered FROM Orders ORDER BY total_ordered DESC;

此外我们可以使用内连接方式:利用 SUM() 函数进行求和,求出每个顾客的订单总数,然后通过条件查询找出两个表中 order_num 相同记录的并通过 cust_id 分组,最后则是将求和的订单总数倒序排列。

SELECT cust_id, SUM(item_price * quantity) AS total_ordered FROM OrderItems, Orders WHERE OrderItems.order_num = Orders.order_num GROUP BY cust_id ORDER BY total_ordered DESC;

致谢

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

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