unions合并多段查询的记录

基于同一个表格写查询将结果合并到一个结果集 

/*我们已经学习了使用join结合表的列
下面学习结合表的行
使用union合并两端查询的数据*/
SELECT 
	order_id,
    order_date,
    'Active' AS staus#注意引号
FROM orders o 
WHERE order_date >= '2019-01-01'
UNION
SELECT 
	order_id,
    order_date,
    'Archived' AS staus#注意引号
FROM orders o 
WHERE order_date < '2019-01-01'

 基于不同表格写查询将结果合并到一个结果集 

SELECT first_name
FROM customers
UNION 
SELECT name
FROM shippers

可以看到列名是基于第一段的查询 

SELECT first_name AS full_name
FROM customers
UNION 
SELECT name
FROM shippers

 练习得到下列查询，顾客表里是没有type列的，注意我们

按照名字排序了。 

SELECT 
	customer_id,
    first_name,
    points,
    'Bronze' AS type
FROM customers
WHERE points < 2000
UNION 
SELECT 
	customer_id,
    first_name,
    points,
    'Silver' AS type
FROM customers
WHERE points BETWEEN 2000 AND 3000
UNION 
SELECT 
	customer_id,
    first_name,
    points,
    'Gold' AS type
FROM customers
WHERE points >3000
ORDER BY first_name

• union--连接表，对行操作。
• • union--将两个表做行拼接，同时自动删除重复的行。
• • union all---将两个表做行拼接，保留重复的行。