【SQL4天必知必会】day3. 子查询、联表查询

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14

93.in + 子查询 【子查询集合作为主查询WHERE条件里IN() 的内容】

返回购买 prod_id 为BR01 的产品的所有顾客的电子邮件（Customers 表中的 cust_email），结果无需排序。
先找到 BR01 产品对应的订单号集合A

（SELECT order_num FROM OrderItems WHERE prod_id = 'BR01'） A
1

从Orders表中过滤出在集合A中的用户ID

SELECT cust_id FROM Orders WHERE order_num IN(A) B
1

拿这个这些用户ID 获取到 需要的用户邮箱

SELECT cust_email FROM Customers WHERE cust_id IN(B)
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'
        ) 
    )
1
2
3
4
5
6

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
1
2
3
4

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
1
2
3
4
5

②

SELECT 
    prod_name, 
    (SELECT SUM(quantity) FROM OrderItems b WHERE b.prod_id = a.prod_id)
FROM Products a
1
2
3
4

二、联表查询

96.返回顾客名称和相关订单号

简单的等联接语法

SELECT cust_name, order_num 
FROM Customers, Orders
WHERE Customers.cust_id = Orders.cust_id
ORDER BY cust_name, order_num
1
2
3
4

使用 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
1
2
3

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
1
2
3
4
5
6

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
1
2
3
4

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
1
2
3
4

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'
1
2
3
4
5
6

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
1
2
3
4
5
6
7
8
9