UNION、UNION ALL的使用练习题 【牛客-SQL必知必会】13 组合查询

---

SQL题解汇总

https://blog.csdn.net/guliguliguliguli/article/details/126109166

题目链接

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

---

SQL 106 将两个 SELECT 语句结合起来（一）——UNION、UNION ALL

答案

本题使用UNION、UNION ALL均可

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 ASC;

SQL 107 将两个 SELECT 语句结合起来（二）

题目又说使用两个SELECT语句，又说只是用一个SELECT…

答案一 使用一个SELECT

SELECT prod_id, quantity
FROM OrderItems
WHERE quantity = 100 OR prod_id LIKE 'BNBG%'
ORDER BY prod_id ASC;

答案二 使用两个SELECT

SELECT prod_id, quantity
FROM OrderItems
WHERE quantity = 100
UNION ALL
SELECT prod_id, quantity
FROM OrderItems
WHERE prod_id LIKE 'BNBG%'
ORDER BY prod_id ASC;

SQL 108 组合 Products 表中的产品名称和 Customers 表中的顾客名称

答案

注意，Customers表需要给字段cust_name取别名

SELECT prod_name FROM Products
UNION ALL
SELECT cust_name prod_name FROM Customers
ORDER BY prod_name;

SQL 109 纠错4

答案一 使用UINON

使用UNION子句进行双重查询，再将两次查询的结果结合在一起，但是由于使用了UNION子句，所以ORDER BY子句只能使用一次且只能在最后一个 SELECT中使用

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;

答案二 使用OR

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