SQL26 确定 Products 表中价格不超过 10 美元的最贵产品的价格

描述

Products 表

prod_price
9.49
600
1000

问题

编写 SQL 语句,确定 Products 表中价格不超过 10 美元的最贵产品的价格(prod_price)。将计算所得的字段命名为 max_price。

示例结果

返回 max_price

max_price
9.49

示例解析

返回十元以下最高价格 max_price。

示例

DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`prod_price` DOUBLE NOT NULL COMMENT '产品价格'
);
INSERT INTO `Products` VALUES (9.49),
(600),
(1000);

解答

考察知识点:

  • 条件查询:使用关键字 WHERE,将产品价格不超过 10 美元的产品筛选出来。
  • MAX(列名):找出列中的最大值。
  • 取别名:通过关键字 AS 将不超过 10 美元的产品中价格最高的记录筛选出来后重命名。
SELECT MAX(prod_price) AS max_price FROM Products WHERE prod_price <= 10;

SQL27 返回每个订单号各有多少行数

描述

OrderItems 表包含每个订单的每个产品

order_num
a002
a002
a002
a004
a007

问题

编写 SQL 语句,返回每个订单号(order_num)各有多少行数(order_lines),并按 order_lines 对结果进行升序排序。

示例结果

返回订单号 order_num 和对应订单号的行数 order_lines

order_num order_lines
a004 1
a007 1
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');

解答

考察知识点:

  • COUNT(列名):返回指定列的值的数目。
  • AS:取别名。
  • GROUP BY:根据指定列或者表达式的值将行进行分组。
  • ORDER BY:根据尾随的列名进行排序,ASC 表示正序,也是默认排序,DESC 表示倒序。
SELECT order_num, COUNT(order_num) AS order_lines FROM OrderItems GROUP BY order_num ORDER BY order_lines;

SQL28 每个供应商成本最低的产品

描述

有Products表,含有字段prod_price代表产品价格,vend_id代表供应商id

vend_id prod_price
a0011 100
a0019 0.1
b0019 1000
b0019 6980
b0019 20

问题

编写 SQL 语句,返回名为 cheapest_item 的字段,该字段包含每个供应商成本最低的产品(使用 Products 表中的 prod_price),然后从最低成本到最高成本对结果进行升序排序。

示例结果

返回供应商 id vend_id 和对应供应商成本最低的产品 cheapest_item。

vend_id cheapest_item
a0019 0.1
b0019 20
a0011 100

示例解析

例如 b0019 成本最低的价格是 20,且最后根据成本价格排序返回依次是 a0019、b0019、a0011。

示例

DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`vend_id` VARCHAR(255) NOT NULL COMMENT '供应商ID',
`prod_price` DOUBLE NOT NULL COMMENT '产品价格'
);
INSERT INTO `Products` VALUES ('a0011',100),
('a0019',0.1),
('b0019',1000),
('b0019',6980),
('b0019',20);

解答

要找出各个供应商中成本最低的产品,则需要通过关键字 GROUP BY 来进行分组,然后借助函数 MIN() 找出 prod_price 中最小的值,接着取别名为 cheapest_item,最后则是按照找出的各供应商中的成本最低产品 cheapest_item 利用关键字 ORDER BY 进行升序排序。

SELECT vend_id, MIN(prod_price) AS cheapest_item FROM Products GROUP BY vend_id ORDER BY cheapest_item;

SQL29 返回订单数量总和不小于100的所有订单的订单号

描述

OrderItems 代表订单商品表,包括:订单号order_num 和订单数量 quantity。

order_num quantity
a1 105
a2 1100
a2 200
a4 1121
a5 10
a2 19
a7 5

问题

请编写 SQL 语句,返回订单数量总和不小于 100 的所有订单号,最后结果按照订单号升序排序。

示例结果

返回 order_num 订单号。

order_num
a1
a2
a4

示例解析

订单号 a1、a2、a4 的 quantity 总和都大于等于 100,按顺序为 a1、a2、a4。

示例

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
quantity INT(255) NOT NULL COMMENT '商品数量'
);
INSERT `OrderItems` VALUES ('a1',105),('a2',200),('a4',1121),('a5',10),('a7',5);

解答

条件查询,只是此时不再是过滤指定的行,而是需要过滤分组,所以这个时候不能再使用关键字 WHERE,而是需要使用到关键字 HAVING,它通常是和关键字 GROUP BY 连用。另外需要注意的是各个关键字之间的先后顺序,先是 GROUP BY,紧接着是 HAVING,最后才是 ORDER BY

SELECT order_num FROM OrderItems GROUP BY order_num HAVING SUM(quantity) >= 100 ORDER BY order_num;

SQL30 计算总和

描述

OrderItems表代表订单信息,包括字段:订单号 order_num 和 item_price 商品售出价格、quantity 商品数量。

order_num item_price quantity
a1 10 105
a2 1 1100
a2 1 200
a4 2 1121
a5 5 10
a2 1 19
a7 7 5

问题

编写 SQL 语句,根据订单号聚合,返回订单总价不小于 1000 的所有订单号,最后的结果按订单号进行升序排序。

提示:总价 = item_price 乘以 quantity

示例结果

order_num total_price
a1 1050
a2 1319
a4 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 ('a1',10,105),('a2',1,1100),('a2',1,200),('a4',2,1121),('a5',5,10),('a2',1,19),('a7',7,5);

解答

以上几题都已经将知识点讲过了,这题只是综合运用,主要涉及如下:

  • SUM():对同一产品的总价求和。
  • AS:取别名。
  • GROUP BY:按照列进行分组。
  • HAVING:与 GROUP BY 联合使用从而实现条件过滤。
  • ORDER BY:按列进行排序。
SELECT order_num, SUM(item_price * quantity) AS total_price FROM OrderItems GROUP BY order_num HAVING total_price >= 1000 ORDER BY order_num;

致谢

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

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