SQL笔记14：组合查询

组合查询

执行多个查询，并将结果作为一个查询结果集返回，成为并（union）或复合查询（compound query）。

创建组合查询

使用关键字UNION组合多条SQL查询。

使用UNION

例1

输入：

SELECT cust_name,cust_contact,cust_email
FROM customers
WHERE cust_state IN ('IL','IN','MI');

输出：

例2

输入：

SELECT cust_name,cust_contact,cust_email
FROM customers
WHERE cust_name='Fun4All';

输出：

组合如下

例3

输入：

SELECT cust_name,cust_contact,cust_email
FROM customers
WHERE cust_state IN ('IL','IN','MI')
UNION
SELECT cust_name,cust_contact,cust_email
FROM customers
WHERE cust_name='Fun4All';

输出：

例4

输入：

SELECT cust_name,cust_contact,cust_email
FROM customers
WHERE cust_state IN ('IL','IN','MI') OR cust_name='Fun4All';

输出：

UNION规则

1. UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分割
2. UNION中的每个查询必须包含相同的带查询项
3. 列数据类型必须兼容：类型可以不同，但必须是DBMS可以隐形转换的类型

包含或取消重复的行

例1 UNION ALL 不取消重复的行

输入：

SELECT cust_name,cust_contact,cust_email
FROM customers
WHERE cust_state IN('IL','IN','MI')
UNION ALL 
SELECT cust_name,cust_contact,cust_email
FROM customers
WHERE cust_name='Fun4All';

输出：

对组合查询结果排序

在用UNION组合查询时，只能使用一条ORDER BY 子句，必须位于最后一条SELECT语句之后。

例1

输入：

SELECT cust_name,cust_contact,cust_email
FROM customers
WHERE cust_state IN('IL','IN','MI')
UNION ALL 
SELECT cust_name,cust_contact,cust_email
FROM customers
WHERE cust_name='Fun4All'
ORDER BY cust_name,cust_contact;

输出：

小结

1. 如何用UNION操作符来组合SELECT语句
2. 对组合结果排序
3. 包含或取消重复的行

挑战题

1.Write a SQL statement that combines two SELECT statements that retrieve product id (prod_id) and quantity from the OrderItems table, one filtering for rows with a quantity of exactly 100, and the other filtering for products with an ID that begins with BNBG. Sort the results by product id.
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;
2.Rewrite the SQL statement you just created to use a single SELECT statement.
SELECT prod_id,quantity FROM orderitems WHERE quantity=100 OR prod_id='BNBG%' ORDER BY prod_id;
3.This one is a little nonsensical, I know, but it does reinforce a note earlier in this lesson. Write a SQL statement which returns and combines product name (prod_name) from Products and customer name (cust_name) from Customers, and sort the result by product name.
SELECT prod_name FROM products UNION SELECT cust_name FROM customers ORDER BY prod_name;
4.What is wrong with the following SQL statement? (Try to figure it out without running it):

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

The ; after the first SELECT statement shouldn’t be there, it is terminating the statement. Also, if sorting SELECT statements combined with UNION only one ORDER BY may be used and it must come after the last 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;