mysql基础入门总结----聚类函数,汇总,分组筛选,ROLLUP
2021/11/16 19:11:57
本文主要是介绍mysql基础入门总结----聚类函数,汇总,分组筛选,ROLLUP,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
聚类函数,汇总,分组筛选,ROLLUP
max() min() sum() avg() count()
select max(invoice_total) as highest, min(invoice_total) as lowest, avg(invoice_total) as average, sum(invoice_total) as total, count(invoice_total * 1.1) as number_of_invoices, count(payment_date) as count_of_payments, -- 空值不会参与计数 count(*) as total_records count(distinct client_id) as number_of_clients from invoices where invoice_date >= '2019-07-01'
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 order by total_sales desc
having 子句
select client_id, sum(invoice_total) as total_sales from invoices group by client_id having total_sales > 500 -- 对于汇总后的字段使用having,使用where会产生找不到total_sales字段的报错 -- having 子句筛选的字段,必须是select子句中有的字段
ROLLUP 子句
select state, city, sum(invoice_total) as total_sales from invoices join clients using (client_id) group by state, city with rollup -- ROLLUP操作符会对每个分组进行求和,这里会看到每个分组的求和,以及整个结果集的求和 (mysql特有) -- 如果使用了ROLLUP操作符,就不能在group by子句中使用别名
这篇关于mysql基础入门总结----聚类函数,汇总,分组筛选,ROLLUP的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-06-25MySQL报错Duplicate entry '0' for key 'PRIMARY'
- 2024-05-29阿里 Canal 实时同步 MySQL 增量数据至 ClickHouse 数据库
- 2024-05-24在Linux下管理MySQL的大小写敏感性
- 2024-04-26MySQL查出时间比实际晚8小时的解决方案
- 2024-04-01JPA不识别MySQL的枚举类型
- 2024-03-30mysql数据库表卡死解决方法
- 2024-03-15MySQL多数据源笔记5-ShardingJDBC实战
- 2024-03-11natural join mysql
- 2024-03-11关于VS2017,VS2015 中利用 EF使用Mysql 不显示数据源问题解决方案
- 2024-02-26mysql 阿里云xb后缀备份文件恢复-icode9专业技术文章分享