https://www.nowcoder.com/exam/oj?page=1&tab=SQL%E7%AF%87&topicId=298
💖牛客题目, SQL必知必会。不断熟悉,不断进步,加油!👍 💬 ⭐️
SQL题目
一、子查询
# 91. 返回购买价格为 10 美元或以上产品的顾客列表 in + 子查询
# 先得到价格为10美元以上的订单, 然后从顾客列表过滤有这些订单的顾客
SELECT cust_id FROM Orders a
WHERE order_num IN
(SELECT order_num FROM OrderItems b
GROUP BY order_num
HAVING SUM(item_price) >= 10)
# 92. 确定哪些订单购买了 prod_id 为 BR01 的产品
# 从订单项目中获取 prod_id 为 BR01 的订单号, 然后从订单列表中过滤出订单号在这个这个集合中的订单
SELECT cust_id, order_date FROM Orders
WHERE order_num
IN (SELECT order_num FROM OrderItems WHERE prod_id = 'BR01')
ORDER BY order_date ASC
93.in + 子查询 【子查询集合作为主查询WHERE条件里IN() 的内容】
返回购买 prod_id 为BR01 的产品的所有顾客的电子邮件(Customers 表中的 cust_email),结果无需排序。
先找到 BR01 产品对应的订单号集合A
(SELECT order_num FROM OrderItems WHERE prod_id = 'BR01') A
从Orders表中过滤出在集合A中的用户ID
SELECT cust_id FROM Orders WHERE order_num IN(A) B
拿这个这些用户ID 获取到 需要的用户邮箱
SELECT cust_email FROM Customers WHERE cust_id IN(B)
最终:
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'
)
)
94. 子查询的结果与主查询拼接
一个客户-订单表, 一个订单-金额表, 返回每个顾客不同订单的总金额 (不是所有订单的总金额,是不同订单的总金额)
SELECT cust_id,
(SELECT SUM(item_price * quantity) FROM OrderItems b WHERE a.order_num = b.order_num) total_ordered
FROM Orders a
ORDER BY total_ordered DESC
95.从 Products 表中检索所有的产品名称以及对应的销售总数
①
SELECT
(SELECT prod_name FROM Products b WHERE a.prod_id = b.prod_id) prod_name,
SUM(quantity) quant_sold
FROM OrderItems a
GROUP BY prod_id
②
SELECT
prod_name,
(SELECT SUM(quantity) FROM OrderItems b WHERE b.prod_id = a.prod_id)
FROM Products a
二、联表查询
96.返回顾客名称和相关订单号
简单的等联接语法
SELECT cust_name, order_num
FROM Customers, Orders
WHERE Customers.cust_id = Orders.cust_id
ORDER BY cust_name, order_num
使用 INNER JOIN ON
SELECT cust_name, order_num
FROM Customers a INNER JOIN Orders b ON a.cust_id = b.cust_id
ORDER BY cust_name, order_num
97.返回顾客名称和相关订单号以及每个订单的总价
三个表联表查询
SELECT c.cust_name, c.order_num, d.quantity*d.item_price OrderTotal
FROM
(SELECT cust_name, order_num
FROM Customers a INNER JOIN Orders b ON a.cust_id = b.cust_id) AS c
INNER JOIN OrderItems d ON c.order_num=d.order_num
ORDER BY c.cust_name, c.order_num ASC
SELECT a.cust_name, b.order_num, c.quantity*c.item_price OrderTotal
FROM Customers a,Orders b,OrderItems c
WHERE a.cust_id = b.cust_id AND b.order_num = c.order_num
ORDER BY a.cust_name, b.order_num ASC
98. 确定哪些订单购买了 prod_id 为 BR01 的产品(二)
INNER JOIN 联表查询+过滤
SELECT a.cust_id, a.order_date
FROM Orders a INNER JOIN OrderItems b ON a.order_num = b.order_num
WHERE b.prod_id = 'BR01'
ORDER BY a.order_date
99.返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件
再试试 简单等连接语法~
SELECT cust_email
FROM Customers, Orders, OrderItems
WHERE
Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num
AND OrderItems.prod_id = 'BR01'
100.确定最佳顾客的另一种方式(二)
联表查询在加个等连接查询
SELECT b.cust_name, a.total_price
FROM
(SELECT a.cust_id, SUM(b.item_price * b.quantity) total_price
FROM Orders a LEFT JOIN OrderItems b ON a.order_num = b.order_num
GROUP BY a.cust_id
HAVING total_price >= 1000) a,
Customers b
WHERE a.cust_id = b.cust_id
ORDER BY a.total_price