SQL 必知必会 50 题（26 - 30）

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

描述

Products 表

问题

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

示例结果

返回 max_price

示例解析

返回十元以下最高价格 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 表包含每个订单的每个产品

问题

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

示例结果

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

示例解析

订单号 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

问题

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

示例结果

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

示例解析

例如 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。

问题

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

示例结果

返回 order_num 订单号。

示例解析

订单号 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 商品数量。

问题

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

提示：总价 = item_price 乘以 quantity

示例结果

示例

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;

致谢

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