当前位置：首页 > news >正文

SQL - 汇总与分组

news 2025/12/2 8:41:15

聚合函数

MySQL自带一堆内置函数，其中一些叫聚合函数，用它们汇总数据，因为它们取某一列的值并聚合它们，导出一个单一值。并且聚合函数只会运行非空值，如果列中有的值是null，它不会被算在内。

max(), min(), avg(), sum(), count()，count(*) 返回表中的所有记录数，count(distinct client_id)具有去重功能，默认不去重

select 'first half of 2019' as date_range,sum(invoice_total) as total_sales,sum(payment_total) as total_payment,sum(invoice_total-payment_total) as want
from invoices
where invoice_date between '2019-01-01' and '2019-06-30'	-- 控制范围，也就是筛选union
select 'second half of 2019' as date_range,sum(invoice_total) as total_sales,sum(payment_total) as total_payment,sum(invoice_total-payment_total) as want
from invoices
where invoice_date between '2019-07-01' and '2019-12-31'union
select 'total' as date_range,sum(invoice_total) as total_sales,sum(payment_total) as total_payment,sum(invoice_total-payment_total) as want
from invoices
where invoice_date between '2019-01-01' and '2019-12-31'

group by 子句
- group by 子句在SQL查询中用于将结果集按一个或多个列进行分组。它的主要作用是将具有相同值的行归为一组，以便对每组数据进行聚合计算（如求和、计数、平均值等）
- ```
-- group by 分组
select date,name as payment_method,sum(amount) as total_payments
from payments p
join payment_methods pm on p.payment_method=pm.payment_method_id
group by date,payment_method
order by date
```

having 子句

将结果集按一个或多个列分组后，再次筛选数据，但是筛选的列名必须是是在select中的

-- having 再次筛选
select date,name as payment_method,sum(amount) as total_payments
from payments p
join payment_methods pm on p.payment_method=pm.payment_method_id
group by date,payment_method
having total_payments>10
order by date

rollup 运算符
- 在分组后，每个组都有用聚合函数运算的值，对应的每个组的汇总和整个结果集的汇总可以使用到 with rollup，如group by client_id with rollup，不过这列名不能使用别名
- ```
-- rollup运算符
select 
client_id,
sum(invoice_total) as total_sales
from invoices
group by client_id with rollup
```