MySQL中的常用聚合函数聚合函数

92 阅读 0 评论 61 点赞

我是靠谱客的博主潇洒玫瑰，这篇文章主要介绍MySQL中的常用聚合函数聚合函数，现在分享给大家，希望可以做个参考。

聚合函数

我们常用的函数其实并不多，重要的是将这些常用函数用好。

聚合函数没有看起来那么高大上，实际上就是一些求和，求平均类似的操作。
常用的聚合函数一般为以下几个：

MAX()
MIN()
AVG()
SUM()
COUNT()

以SUM()为例，这边直接上代码，实际使用格式是相似的：

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SELECT
'First half of 2019' AS date_range,
SUM(invoice_total) AS total_sales,
SUM(payment_total) AS total_payments,
SUM(invoice_total - payment_total) AS what_we_expect
FROM invoices i
WHERE i.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_payments,
SUM(invoice_total - payment_total) AS what_we_expect
FROM invoices i
WHERE i.invoice_date
BETWEEN '2019-06-30' AND '2020-01-01'
UNION
SELECT
'Total' AS date_range,
SUM(invoice_total) AS total_sales,
SUM(payment_total) AS total_payments,
SUM(invoice_total - payment_total) AS what_we_expect
FROM invoices i
WHERE i.invoice_date
BETWEEN '2019-01-01' AND '2019-12-31'