第三章 13-联合unions--Mosh mySQL

join：连接表，结合多张表（可以是不同张表也可以是同一张）的行：union，union适用于查询多个结果。union之后的列名跟第一个查询的列名是一样的，如果需要更改，则调换顺序；如果需要重命名，则用as，比如select first_name as full_name.

1，在每个订单边添加一个标签，今年的订单active,之前的arichive

use sql_store;
select
order_id,
order_date,
'Active' as status
from orders
where order_date >='2019-01-01'

select
order_id,
order_date,
'Archived' as status
from orders
where order_date <'2019-01-01'

2，两端代码之间加上union，则结果相加

use sql_store;
select
order_id,
order_date,
'Active' as status
from orders
where order_date >='2019-01-01'
union
select
order_id,
order_date,
'Archived' as status
from orders
where order_date <'2019-01-01'

3，不同的表的union。以下例子，从数据库中可以看到客户信息一共10人，发货一共5人，因此结果是10行。

select first_name
from customers
union
select name
from shippers

4，查询返回的列的数量要一样，否则会得到错误提示，如Error Code: 1222. The used SELECT statements have a different number of columns

举例：

select first_name ,last_name
from customers
union
select name
from shippers

练习

points<=2000,Bronze

2000<points<=3000,Silver

points>3000,Gold

答案：

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