基于同一个表格写查询将结果合并到一个结果集
/*我们已经学习了使用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---将两个表做行拼接,保留重复的行。