SQL 必知必会 50 题(41 - 45)
SQL41 确定最佳顾客的另一种方式(二)
描述
OrderItems 表代表订单信息,确定最佳顾客的另一种方式是看他们花了多少钱,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 |
Orders 表含有字段 order_num 订单号、cust_id 顾客 id
order_num | cust_id |
---|---|
a1 | cust10 |
a2 | cust1 |
a3 | cust2 |
a4 | cust22 |
a5 | cust221 |
a7 | cust2217 |
顾客表 Customers 有字段 cust_id 客户 id、cust_name 客户姓名
cust_id | cust_name |
---|---|
cust10 | andy |
cust1 | ben |
cust2 | tony |
cust22 | tom |
cust221 | an |
cust2217 | hex |
问题
编写 SQL 语句,返回订单总价不小于 1000 的客户名称和总额(OrderItems 表中的 order_num)。
提示:需要计算总和(item_price 乘以 quantity)。按总额对结果进行排序,请使用INNER JOIN 语法。
示例结果
cust_name | total_price |
---|---|
andy | 1050 |
ben | 1319 |
tom | 2242 |
示例解析
总额(item_price 乘以 quantity)大于等于 1000 的订单号,例如 a2 对应的顾客id 为 cust1,cust1 的顾客名称 cust_name 是 ben,最后返回 ben 作为 order_num a2 的quantity * item_price 总和的结果 1319。
示例
DROP TABLE IF EXISTS `OrderItems`; |
解答
主要考察的知识点:
- 取别名:
AS
关键字 - 求和:
SUM()
GROUP BY
和HAVING
同时使用用于过滤结果- 排序:
ORDER BY
关键字
主要考察对多个 SQL 关键字的运用,同时还需要注意 SQL 中关键字的前后顺序。
SELECT cust_name, SUM(item_price * quantity) AS total_price |
SQL42 检索每个顾客的名称和所有的订单号(一)
描述
Customers 表代表顾客信息含有顾客 id cust_id 和顾客名称 cust_name
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 |
问题
使用 INNER JOIN 编写 SQL 语句,检索每个顾客的名称(Customers 表中的 cust_name)和所有的订单号(Orders 表中的 order_num),最后根据顾客姓名 cust_name 升序返回。
示例结果
返回顾客名称 cust_name 和订单号 order_num
cust_name | order_num |
---|---|
an | a5 |
andy | a1 |
ben | a2 |
hex | a7 |
tom | a4 |
tony | a3 |
示例
DROP TABLE IF EXISTS `Customers`; |
解答
主要考察 INNER JOIN
的用法,其使用语法如下:
SELECT column_name(s) |
要实现本题,利用上述语法填入对应字段名和表名即可,最后则是关键字 ORDER BY
的使用。
SELECT cust_name, Orders.order_num FROM Customers JOIN Orders ON Orders.cust_id = Customers.cust_id ORDER BY cust_name; |
SQL43 检索每个顾客的名称和所有的订单号(二)
描述
Orders 表代表订单信息含有订单号 order_num和顾客 id cust_id
order_num | cust_id |
---|---|
a1 | cust10 |
a2 | cust1 |
a3 | cust2 |
a4 | cust22 |
a5 | cust221 |
a7 | cust2217 |
Customers 表代表顾客信息含有顾客 id cust_id 和 顾客名称 cust_name
cust_id | cust_name |
---|---|
cust10 | andy |
cust1 | ben |
cust2 | tony |
cust22 | tom |
cust221 | an |
cust2217 | hex |
cust40 | ace |
问题
检索每个顾客的名称(Customers 表中的 cust_name)和所有的订单号(Orders 表中的 order_num),列出所有的顾客,即使他们没有下过订单。最后根据顾客姓名 cust_name 升序返回。
示例结果
返回顾客名称 cust_name 和订单号 order_num
cust_name | order_num |
---|---|
ace | NULL |
an | a5 |
andy | a1 |
ben | a2 |
hex | a7 |
tom | a4 |
tony | a3 |
示例解析
基于两张表,返回订单号 a1 的顾客名称 andy 等人,没有下单的顾客 ace 也统计了进来。
示例
DROP TABLE IF EXISTS `Customers`; |
解答
根据题意,主要以 Customers
表中的列为主,然后取 Customers
和 Orders
中的交集。对于 Orders
表中不存在的列则取值 null
。所以可以使用外联结中的 LEFT JION
,其使用语法如下:
SELECT column_name(s) |
套用上述语法,填入对应表和列名即可实现,最后则是再加入简单的排序即可。
SELECT cust_name, Orders.order_num FROM Customers LEFT JOIN Orders On Orders.cust_id = Customers.cust_id ORDER BY cust_name; |
SQL44 返回产品名称和与之相关的订单号
描述
Products 表为产品信息表含有字段 prod_id 产品 id、prod_name 产品名称
prod_id | prod_name |
---|---|
a0001 | egg |
a0002 | sockets |
a0013 | coffee |
a0003 | cola |
a0023 | soda |
OrderItems 表为订单信息表含有字段 order_num 订单号和产品 id prod_id
prod_id | order_num |
---|---|
a0001 | a105 |
a0002 | a1100 |
a0002 | a200 |
a0013 | a1121 |
a0003 | a10 |
a0003 | a19 |
a0003 | a5 |
问题
使用 OUTER JOIN 联结 Products 表和 OrderItems 表,返回产品名称(prod_name)和与之相关的订单号(order_num)的列表,并按照产品名称升序排序。
示例结果
返回产品名称 prod_name 和订单号 order_num
prod_name | order_num |
---|---|
coffee | a1121 |
cola | a5 |
cola | a19 |
cola | a10 |
egg | a105 |
sockets | a200 |
sockets | a1100 |
soda | NULL |
示例解析
返回产品和对应实际支付订单的订单号,但是无实际订单的产品 soda 也返回,最后根据产品名称升序排序。
示例
DROP TABLE IF EXISTS `Products`; |
解答
此题解法类似于 43 题,主要使用 OUTER JOIN
中的左联结 LEFT JOIN
,主要清除相关语法,然后套用填入表名和列名即可。
SELECT prod_name, OrderItems.order_num FROM Products |
SQL45 返回产品名称和每一项产品的总订单数
描述
Products 表为产品信息表含有字段 prod_id 产品 id、prod_name 产品名称
prod_id | prod_name |
---|---|
a0001 | egg |
a0002 | sockets |
a0013 | coffee |
a0003 | cola |
a0023 | soda |
OrderItems 表为订单信息表含有字段 order_num 订单号和产品 id prod_id
prod_id | order_num |
---|---|
a0001 | a105 |
a0002 | a1100 |
a0002 | a200 |
a0013 | a1121 |
a0003 | a10 |
a0003 | a19 |
a0003 | a5 |
问题
使用 OUTER JOIN 联结 Products 表和 OrderItems 表,返回产品名称(prod_name)和每一项产品的总订单数(不是订单号),并按产品名称升序排序。
示例结果
返回产品名称 prod_name 和订单号订单数 orders
prod_name | orders |
---|---|
coffee | 1 |
cola | 3 |
egg | 1 |
sockets | 2 |
soda | 0 |
示例解析
返回产品和产品对应的实际支付的订单数,但是无实际订单的产品 soda 也返回,最后根据产品名称升序排序。
示例
DROP TABLE IF EXISTS `Products`; |
解答
考察的知识点仍然是外连接,只不过加入了 COUNT()
函数用于分组统计,最后同样是简单的排序。
SELECT prod_name, COUNT(order_num) AS orders |
致谢
感谢牛客网提供的题目列表。
