【SQL4天必知必会】day4. 高级联结、组合查询

https://www.nowcoder.com/exam/oj?page=1&tab=SQL%E7%AF%87&topicId=298
💖牛客题目， SQL必知必会。不断熟悉，不断进步，加油！👍 💬 ⭐️

SQL题目

一、高级联接

101. inner join on

检索每个顾客的名称和所有的订单号一

SELECT a.cust_name, b.order_num 
FROM Customers a INNER JOIN Orders b ON a.cust_id = b.cust_id
ORDER BY a.cust_name

102. right / left join on

检索每个顾客的名称和所有的订单号二
（列出所有顾客，即使顾客没下过单）

SELECT b.cust_name, a.order_num
FROM Orders a RIGHT JOIN Customers b ON a.cust_id = b.cust_id
ORDER BY cust_name

103. outer join

MySQL 没有 outer join， 用 union 左右外连接

SELECT prod_name, order_num
FROM Products a LEFT JOIN OrderItems b ON a.prod_id = b.prod_id
UNION
SELECT prod_name, order_num
FROM OrderItems a RIGHT JOIN Products b ON a.prod_id = b.prod_id
ORDER BY prod_name

104. 联表+计算

返回产品名称和每一项产品的总订单数

SELECT prod_name, COUNT(order_num) orders
FROM Products a LEFT JOIN OrderItems b ON a.prod_id = b.prod_id
GROUP BY prod_name
ORDER BY prod_name

105. 同上题

SELECT a.vend_id , COUNT(prod_id) prod_id
FROM Vendors a LEFT JOIN Products b ON a.vend_id = b.vend_id
GROUP BY vend_id
ORDER BY vend_id 

二、组合查询

106. UNION 的使用

将两个 SELECT 语句结合起来一

SELECT prod_id, quantity
FROM OrderItems WHERE quantity = 100
UNION
SELECT prod_id, quantity
FROM OrderItems WHERE prod_id like 'BNBG%'
ORDER BY prod_id

UNION 会自动去重， UNION ALL 不去重

107.

将两个 SELECT 语句结合起来二

SELECT * FROM OrderItems WHERE quantity = 100 OR prod_id like 'BNBG%'

108. UNION

组合 Products 表中的产品名称（prod_name）和 Customers 表中的顾客名称（cust_name）并返回，然后按产品名称对结果进行升序排序。

SELECT prod_name a FROM Products
UNION 
SELECT cust_name a FROM Customers
ORDER BY a

108. SQL 纠错

用 UNION 最后一个ORDER BY, 不能每个SELECT 都有自己的一个 ORDER BY.

SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state = 'MI' 
UNION 
SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state = 'IL'
ORDER BY cust_name;