SQL 必知必会第十四课 组合查询

SQL 必知必会第十四课 组合查询

select cust_name, cust_contact, cust_email
from customers
where cust_state in('IL','IN','MI');

select cust_name, cust_contact, cust_email
from customers
where cust_name = 'Fun4All';

select cust_name, cust_contact, cust_email,cust_state
from customers
where cust_state in('IL','IN','MI')
union
select cust_name, cust_contact, cust_email,cust_state
from customers
where cust_name = 'Fun4All';

select cust_name, cust_contact, cust_email,cust_state
from customers
where cust_state in('IL','IN','MI')
union all
select cust_name, cust_contact, cust_email,cust_state
from customers
where cust_name = 'Fun4All'; -- DBMS 不取消重复的行

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'
order by cust_name, cust_contact; -- order by 排序的是所有选中的行


## challenges

select prod_id, quantity 
from OrderItems
where quantity >= 100
union 
select prod_id, quantity
from OrderItems
where prod_id = 'BNBG'
order by prod_id;-- 1

select prod_id, quantity 
from orderitems
where quantity >= 100 or prod_id = 'BNBG'
order by prod_id; -- 2

select prod_name 
from products
union
select cust_name 
from customers
order by prod_name; -- 3

注意点：

1. union 必须由两条或两条以上的select语句组成，与句之间用关键词分割

2. union每个查询必须包含相同的列，表达式或聚集函数

3. 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含的转换的类型