GROUP BY

group by语法可以根据给定数据列的每个成员对查询结果进行分组统计，最终得到一个分组汇总表。

SELECT子句中的列名必须为分组列或列函数。列函数对于GROUP BY子句定义的每个组各返回一个结果。

可应用限定条件进行分组，以便系统仅对满足条件的组返回结果。

SELECT col_1, col_2, MAX(col_3) AS alis -- 分组后使用聚合函数作用与组内的数据
FROM tab
WHERE col_1 > 'xxx'
GROUP BY col_1, col_2 -- 把列1，列2 看成一个整体，分组
having col_1 > 'xxx' -- 在每一组中进行筛选
ORDER BY col_1, col_2

Table: Employees

Role	Name	Building	Years_employed
Engineer	Becky A.	1e	4
Engineer	Dan B.	1e	2
Engineer	Sharon F.	1e	6
Engineer	Dan M.	1e	4
Engineer	Malcom S.	1e	1
Artist	Tylar S.	2w	2
Artist	Sherman D.	2w	8
Artist	Jakob J.	2w	6
Artist	Lillia A.	2w	7
Artist	Brandon J.	2w	7
Manager	Scott K.	1e	9
Manager	Shirlee M.	1e	3
Manager	Daria O.	2w	6

计算Engineer的总工作年限

Role	Years
Engineer	17

SELECT role, SUM(Years_employed) Years FROM employees
GROUP BY role
HAVING role='Engineer'

JOIN

OrderItems表

prod_id	order_num
BR01	a0001
BR01	a0002
BR02	a0003
BR02	a0013

Orders表

order_num	cust_id	order_date
a0001	cust10	2022-01-01 00:00:00
a0002	cust1	2022-01-01 00:01:00
a0003	cust1	2022-01-02 00:00:00
a0013	cust2	2022-01-01 00:20:00

确定哪些订单（在 OrderItems 中）购买了 prod_id 为 "BR01" 的产品，然后从 Orders 表中返回每个产品对应的顾客 ID（cust_id）和订单日期（order_date），按订购日期对结果进行升序排序。

结果

cust_id	order_date
cust10	2022-01-01 00:00:00
cust1	2022-01-01 00:01:00

where

select cust_id, order_date
from Orders o, OrderItems oi
where prod_id = 'BR01' and o.order_num = oi.order_num
order by order_date

子查询

select cust_id, order_date from Orders
where order_num in (
  select order_num from OrderItems
  where prod_id = 'BR01'
)
order by order_date;

左连接

select cust_id,order_date
from Orders o
left join OrderItems oi 
on o.order_num = oi.order_num
where prod_id = 'BR01'
order by order_date;

自然连接

select cust_id, order_date
from Orders
natural join OrderItems
where prod_id = 'BR01'
order by  order_date;

内连接

select cust_id, order_date
from Orders o
inner JOIN OrderItems oi
on o.order_num = oi.order_num and  prod_id = 'BR01'
order by order_date;

自然连接 using

select cust_id, order_date
from Orders
join OrderItems using(order_num)
where prod_id = 'BR01'
order by order_date;

UNION

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

表OrderItems包含订单产品信息，字段prod_id代表产品id、quantity代表产品数量

prod_id	quantity
a0001	105
a0002	100
a0002	200
a0013	1121
a0003	10
a0003	19
a0003	5
BNBG	10002

【问题】

将两个 SELECT 语句结合起来，以便从 OrderItems表中检索产品 id（prod_id）和 quantity。其中，一个 SELECT 语句过滤数量为 100 的行，另一个 SELECT 语句过滤 id 以 BNBG 开头的产品，最后按产品 id 对结果进行升序排序。

【示例结果】

返回产品id prod_id和产品数量quantity

prod_id	quantity
a0002	100
BNBG	10002

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