SQL - 汇总与分组

ops/2024/9/24 3:25:53/

聚合函数

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

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

sql">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查询中用于将结果集按一个或多个列进行分组。它的主要作用是将具有相同值的行归为一组，以便对每组数据进行聚合计算（如求和、计数、平均值等）
- ```
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中的

sql">-- 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，不过这列名不能使用别名
- ```
sql">-- rollup运算符
select 
client_id,
sum(invoice_total) as total_sales
from invoices
group by client_id with rollup
```

SQL - 汇总与分组

相关文章

keepalived讲解及练习

如何去除抖音视频水印,还原视频的3种方法

HAProxy的详解

【网络 day1】

C2M商业模式分析与运营平台建设解决方案（三）

【MySQL】5.0 入门学习（五）——MySQL源码了解及MySQL初始化设置

Spring Environment学习

汇编语言中的艺术：数据压缩与解压缩技术