投稿
  1. 极客之音首页
  2. 技术随笔

不知道 MySQL 咋学？刷完牛客这 50 道题就够了！(第七篇)

Java光头强 • • 技术随笔 • 阅读 149

导读：本篇文章讲解 不知道 MySQL 咋学？刷完牛客这 50 道题就够了！(第七篇)，希望对大家有帮助，欢迎收藏，转发！站点地址：www.bmabk.com

本次内容共分为十篇，每次五题，其它篇章在主页Mysql专栏中

前言

你是否还在烦恼 SQL 该从何学起，或者学了 SQL 想找个地方练练手？好巧不巧，最近在工作之余登上牛客，发现了牛客不知道啥时候上线了SQL 必知必会的练习题。

《SQL 必知必会》作为麻省理工学院、伊利诺伊大学等众多大学的参考教材，由浅入深地讲解了SQL的基本概念和语法。涉及数据的排序、过滤和分组，以及表、视图、联结、子查询、游标、存储过程和触发器等内容。实例丰富，方便查阅，可以说作为一个 CRUD BOY/GIRL 必读书目。

想着正好给它刷一遍，然后将自己刷题的一些想法总结下，于是有了今天这篇文章，希望能给需要的小伙伴一点点帮助

不知道 MySQL 咋学？刷完牛客这 50 道题就够了！(第七篇)

 
 

SQL31 纠错3

描述

OrderItems 表含有 order_num 订单号

order_num
a002
a002
a002
a004
a007

问题

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

1

2

3

4

5

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

示例

1

2

3

4

5

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 语句所执行的效率。

1

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

1

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

示例

1

2

3

4

5

6

7

8

9

10

11

12

13

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。

1

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

示例

1

2

3

4

5

6

7

8

9

10

11

12

13

14

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_id 为 BR01 的记录 ，然后再从 Orders 表中筛选出 order_num 为子查询结果集中的记录，最后按照 order_date 进行排序即可。主要是通过对条件查询语句的嵌套使用，从而实现多重筛选。

1

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

示例

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

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 个条件查询后，从内向外依次查询，然后基于上一层查询结果再做条件过滤。

1

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。

示例

1

2

3

4

5

6

7

8

9

10

11

12

13

14

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

解答

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

1

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 分组，最后则是将求和的订单总数倒序排列。

1

SELECT cust_id, SUM(item_price * quantity) AS total_ordered FROM OrderItems, Order

 

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

文章由极客之家整理，本文链接：https://www.bmabk.com/index.php/post/116112.html

赞 (0)
Java光头强的头像Java光头强
0 0

相关推荐

发表回复

登录后才能评论
极客之家——专业性很强的中文编程技术网站，欢迎收藏到浏览器，订阅我们！