---

题目链接

https://www.nowcoder.com/exam/oj/ta?tpId=298

---

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

答案一 INNER JOIN

SELECT cust_name, order_num
FROM Customers c INNER JOIN Orders o ON c.cust_id = o.cust_id
ORDER BY cust_name ASC, order_num ASC;
1
2
3

答案二 简单的等联结语法

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

SQL 97 返回顾客名称和相关订单号以及每个订单的总价

答案一

SELECT c.cust_name,o.order_num,quantity * item_price OrderTotal
FROM Customers c,Orders o,OrderItems oi
WHERE c.cust_id = o.cust_id AND o.order_num = oi.order_num
ORDER BY cust_name ASC, order_num ASC;
1
2
3
4

答案二（优化后）

这种答案是避免OrderItems表中有重复的order_num；答案一中默认OrderItems表中没有重复的order_num

SELECT c.cust_name, o.order_num, SUM(quantity * item_price) OrderTotal
FROM Customers c,Orders o,OrderItems os
WHERE c.cust_id = o.cust_id AND o.order_num = os.order_num
GROUP BY c.cust_name, o.order_num
ORDER BY c.cust_name ASC, o.order_num ASC;
1
2
3
4
5

答案三（子查询）

SELECT c.cust_name,o.order_num,OrderTotal
FROM Customers c, Orders o,(
    SELECT order_num,SUM(quantity*item_price) OrderTotal
    FROM OrderItems 
    GROUP BY order_num) t
WHERE c.cust_id = o.cust_id AND o.order_num = t.order_num
ORDER BY c.cust_name ASC, o.order_num ASC;
1
2
3
4
5
6
7

SQL 98 确定哪些订单购买了 prod_id 为 BR01 的产品（二）

答案一

SELECT cust_id, order_date
FROM Orders o1, OrderItems o2
WHERE o1.order_num = o2.order_num
AND prod_id = 'BR01'
ORDER BY order_date ASC;
1
2
3
4
5

答案二

SELECT cust_id, order_date
FROM Orders o1 JOIN OrderItems o2
ON o1.order_num = o2.order_num
WHERE prod_id = 'BR01'
ORDER BY order_date ASC;
1
2
3
4
5

SQL 99 返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（二）

答案

SELECT cust_email
FROM Customers c INNER JOIN Orders o1 ON c.cust_id = o1.cust_id
INNER JOIN OrderItems o2 ON o1.order_num = o2.order_num
WHERE prod_id = 'BR01';
1
2
3
4

SQL 100 确定最佳顾客的另一种方式（二）

答案一（过滤条件放在子查询外面）

SELECT cust_name, total_price
FROM Customers c
JOIN Orders o1 ON c.cust_id = o1.cust_id
JOIN (
    SELECT order_num, SUM(item_price * quantity) total_price
    FROM OrderItems
    GROUP BY order_num
) o2 ON o1.order_num = o2.order_num
WHERE total_price >=1000
ORDER BY total_price ASC;
1
2
3
4
5
6
7
8
9
10

答案二（过滤条件放在子查询里面）

个人感觉过滤条件放在子查询里面好一点，在形成子表的时候就已经是一个比较小的表了，再连接其他表，开销就会比较小。

SELECT cust_name, total_price
FROM Customers c
JOIN Orders o1 ON c.cust_id = o1.cust_id
JOIN (
    SELECT order_num, SUM(item_price * quantity) total_price
    FROM OrderItems
    GROUP BY order_num
    HAVING total_price >= 1000
) o2 ON o1.order_num = o2.order_num
ORDER BY total_price ASC;
1
2
3
4
5
6
7
8
9
10

---