mysql详解4：分组

聚合函数
SUM 求和 COUNT计数
DISTNCT 去重
select COUNT(DISTNCT client_id) as total_records
from invoices where invoice_date>‘2018-04-02’查询并去重

分组
group by
根据多列分组
select state,city,SUM(invoice_total) as total_sales
from invoices
JOIN clients using (client_id)
where invoice_date >='2019-07-01'
GROUP BY state,city

 

having 是分组后对数据筛选 where是分组前筛选
having 只能提到选择的列

 

rollup 汇总 只能用于复合函数
多列复合 会得到整个组的的汇总和各个组的汇总
with rollup 不能使用别名

select client_id,
sum(invoice_total) total
from invoices
GROUP BY client_id with ROLLUP