MySQL 聚合函數(shù)及應(yīng)用

更新時(shí)間：2026年05月27日 10:40:59 作者：無(wú)限進(jìn)步D

這段文章詳細(xì)介紹了聚合函數(shù)在SQL中的應(yīng)用,包括AVG、SUM、MIN、MAX和 COUNT等、MAX和 COUNT的的用、MAX和 COUNT)的使用場(chǎng)景和 COUNT)的的使用場(chǎng)景,并解釋了HAVING和WHERE的區(qū)別和 COUNT)的使用場(chǎng)景,以及SQL查詢(xún)語(yǔ)句的執(zhí)行順序和優(yōu)化技巧,感興趣的朋友一起看看吧

1. 常見(jiàn)的聚合函數(shù)

作用： 作用于一組數(shù)據(jù)，并對(duì)一組數(shù)據(jù)返回一個(gè)值

語(yǔ)法：

聚合函數(shù)不能嵌套調(diào)用
- 比如不能出現(xiàn)類(lèi)似“AVG(SUM(字段名稱(chēng)))”形式的調(diào)用

1.1 AVG和SUM函數(shù)

對(duì)象：數(shù)值型數(shù)據(jù)
作用：AVG平均值，SUM總和

mysql> SELECT
    ->  AVG(salary) '平均工資',
    ->  SUM(salary) '總工資'
    -> FROM
    ->  employees;
+-------------+-----------+
| 平均工資     | 總工資     |
+-------------+-----------+
| 6461.682243 | 691400.00 |
+-------------+-----------+
1 row in set (0.01 sec)

注意：如果應(yīng)用于字符串等類(lèi)型，不會(huì)報(bào)錯(cuò)，但是結(jié)果沒(méi)有意義

1.2 MIN和MAX函數(shù)

對(duì)象：任意數(shù)據(jù)類(lèi)型（數(shù)值、字符串、時(shí)間日期……）
作用：MIN最小值，MAX最大值

數(shù)值即最大、最小的數(shù)值

mysql> SELECT
    ->  MAX(salary) '最高工資'
    -> ,MIN(salary) '最低工資'
    -> FROM
    ->  employees;
+----------+----------+
| 最高工資  | 最低工資  |
+----------+----------+
| 24000.00 |  2100.00 |
+----------+----------+
1 row in set (0.00 sec)

字符串按字典序

mysql> SELECT
    ->  MAX(last_name)
    -> FROM
    ->  employees;
+----------------+
| MAX(last_name) |
+----------------+
| Zlotkey        |
+----------------+
1 row in set (0.00 sec)

1.3 COUNT函數(shù)

對(duì)象：任意數(shù)據(jù)類(lèi)型
作用：返回表中記錄總數(shù)

COUNT(*)返回表中記錄總數(shù)，適用于任意數(shù)據(jù)類(lèi)型

mysql> SELECT COUNT(*) FROM employees;
+----------+
| COUNT(*) |
+----------+
|      107 |
+----------+
1 row in set (0.00 sec)

COUNT(expr) 返回expr不為空的記錄總數(shù)

mysql> SELECT
    ->  COUNT(commission_pct)
    -> FROM
    ->  employees;
+-----------------------+
| COUNT(commission_pct) |
+-----------------------+
|                    35 |
+-----------------------+
1 row in set (0.00 sec)

Q&A：

Q：AVG(xxx) 等于 SUM(xxx) / COUNT(xxx) 嗎？
A：等于，AVG()和SUM()也會(huì)過(guò)濾NULL

Q：count(*)，count(1)，count(列名)用誰(shuí)好？
A1：對(duì)于MyISAM引擎的表是沒(méi)有區(qū)別的，引擎內(nèi)部有一計(jì)數(shù)器在維護(hù)著行數(shù)
A2：但若是Innodb引擎的表用count(*),count(1)直接讀行數(shù)，復(fù)雜度是 O(n)O(n)O(n) ，因?yàn)閕nnodb真的要去數(shù)一遍，但好于具體的count(列名)

Q：能不能使用count(列名)替換count(*)?
A：不要使用 count(列名)來(lái)替代 count(*)
count(*)是 SQL92 定義的標(biāo)準(zhǔn)統(tǒng)計(jì)行數(shù)的語(yǔ)法，跟數(shù)據(jù)庫(kù)無(wú)關(guān)，跟 NULL 和非 NULL 無(wú)關(guān)
count(*)會(huì)統(tǒng)計(jì)值為 NULL 的行，而 count(列名)不會(huì)統(tǒng)計(jì)此列為 NULL 值的行

練習(xí)：
查詢(xún)公司中的平均獎(jiǎng)金率

mysql> SELECT
    ->  SUM(commission_pct)/COUNT(IFNULL(commission_pct,0)) '平均獎(jiǎng)金率',
    ->  AVG(IFNULL(commission_pct,0)) '平均獎(jiǎng)金率'
    -> FROM
    ->  employees;
+------------+------------+
| 平均獎(jiǎng)金率  | 平均獎(jiǎng)金率   |
+------------+------------+
|   0.072897 |   0.072897 |
+------------+------------+
1 row in set (0.00 sec)

注意：AVG()會(huì)把NULL去掉，但平均獎(jiǎng)金率應(yīng)該把0也算進(jìn)去

2. GROUP BY

group 組

2.1 基本使用

可以使用GROUP BY子句將表中的數(shù)據(jù)分成若干組

mysql> SELECT
    ->  department_id,
    ->  AVG( salary )
    -> FROM
    ->  employees
    -> GROUP BY
    ->  department_id;
+---------------+---------------+
| department_id | AVG( salary ) |
+---------------+---------------+
|          NULL |   7000.000000 |
|            10 |   4400.000000 |
|            20 |   9500.000000 |
|            30 |   4150.000000 |
|            40 |   6500.000000 |
|            50 |   3475.555556 |
|            60 |   5760.000000 |
|            70 |  10000.000000 |
|            80 |   8955.882353 |
|            90 |  19333.333333 |
|           100 |   8600.000000 |
|           110 |  10150.000000 |
+---------------+---------------+
12 rows in set (0.00 sec)

2.2 使用多個(gè)列分組

mysql> SELECT
    ->  department_id,
    ->  job_id,
    ->  SUM( salary )
    -> FROM
    ->  employees
    -> GROUP BY
    ->  department_id,
    ->  job_id;
+---------------+------------+---------------+
| department_id | job_id     | SUM( salary ) |
+---------------+------------+---------------+
|            90 | AD_PRES    |      24000.00 |
|            90 | AD_VP      |      34000.00 |
|            60 | IT_PROG    |      28800.00 |
|           100 | FI_MGR     |      12000.00 |
|           100 | FI_ACCOUNT |      39600.00 |
|            30 | PU_MAN     |      11000.00 |
|            30 | PU_CLERK   |      13900.00 |
|            50 | ST_MAN     |      36400.00 |
|            50 | ST_CLERK   |      55700.00 |
|            80 | SA_MAN     |      61000.00 |
|            80 | SA_REP     |     243500.00 |
|          NULL | SA_REP     |       7000.00 |
|            50 | SH_CLERK   |      64300.00 |
|            10 | AD_ASST    |       4400.00 |
|            20 | MK_MAN     |      13000.00 |
|            20 | MK_REP     |       6000.00 |
|            40 | HR_REP     |       6500.00 |
|            70 | PR_REP     |      10000.00 |
|           110 | AC_MGR     |      12000.00 |
|           110 | AC_ACCOUNT |       8300.00 |
+---------------+------------+---------------+
20 rows in set (0.00 sec)

注意：

SELECT 中出現(xiàn)的非組函數(shù)的字段必須聲明在GROUP BY 中
GROUP BY 子句中聲明的字段可以不出現(xiàn)在SELECT 中
GROUP BY 聲明在 FROM 、WHERE 后面，ORDER BY、LIMIT 前面

2.3 GROUP BY中使用WITH ROLLUP

使用 WITH ROLLUP 關(guān)鍵字之后，在所有查詢(xún)出的分組記錄之后增加一條記錄，該記錄計(jì)算查詢(xún)出的所有記錄的總和，即統(tǒng)計(jì)記錄數(shù)量

mysql> SELECT
    ->  department_id,
    ->  AVG( salary )
    -> FROM
    ->  employees
    -> WHERE
    ->  department_id > 80
    -> GROUP BY
    ->  department_id WITH ROLLUP;
+---------------+---------------+
| department_id | AVG( salary ) |
+---------------+---------------+
|            90 |  19333.333333 |
|           100 |   8600.000000 |
|           110 |  10150.000000 |
|          NULL |  11809.090909 |# 多的一行
+---------------+---------------+
# 算的平均，所以這里是總的平均值
4 rows in set (0.00 sec)

[!注意]
當(dāng)使用ROLLUP時(shí)，不能同時(shí)使用ORDER BY子句進(jìn)行結(jié)果排序，即ROLLUP和ORDER BY是互相排斥的

3. HAVING

注意： WHERE不能使用聚合函數(shù)，所以過(guò)濾條件中出現(xiàn)聚合函數(shù)，就得用HAVING

過(guò)濾分組： HAVING子句

行已經(jīng)被分組
使用聚合函數(shù)
滿(mǎn)足HAVING 子句中條件的分組將被顯示
HAVING 不能單獨(dú)使用，必須要跟 GROUP BY 一起使用，且HAVING 必須聲明在 GROUP BY 后面

mysql> SELECT
    ->  department_id,
    ->  MAX( salary )
    -> FROM
    ->  employees
    -> GROUP BY
    ->  department_id
    -> HAVING
    ->  MAX( salary )> 10000;
+---------------+---------------+
| department_id | MAX( salary ) |
+---------------+---------------+
|            20 |      13000.00 |
|            30 |      11000.00 |
|            80 |      14000.00 |
|            90 |      24000.00 |
|           100 |      12000.00 |
|           110 |      12000.00 |
+---------------+---------------+
6 rows in set (0.00 sec)

練習(xí)：查詢(xún)部門(mén)id為10,20,30,40這4個(gè)部門(mén)中最高工資比10000高的部門(mén)信息息

方法一：用WHERE

mysql> SELECT
    ->  department_id,
    ->  MAX( salary )
    -> FROM
    ->  employees
    -> WHERE
    ->  department_id IN(10,20,30,40)
    -> GROUP BY
    ->  department_id
    -> HAVING
    ->  MAX( salary )> 10000;
+---------------+---------------+
| department_id | MAX( salary ) |
+---------------+---------------+
|            20 |      13000.00 |
|            30 |      11000.00 |
+---------------+---------------+
2 rows in set (0.00 sec)

方法二：用HAVING

mysql> SELECT
    ->  department_id,
    ->  MAX( salary )
    -> FROM
    ->  employees
    -> GROUP BY
    ->  department_id
    -> HAVING
    ->  MAX( salary )> 10000
    ->  AND department_id IN ( 10, 20, 30, 40 );
+---------------+---------------+
| department_id | MAX( salary ) |
+---------------+---------------+
|            20 |      13000.00 |
|            30 |      11000.00 |
+---------------+---------------+
2 rows in set (0.00 sec)

推薦使用方式一，方式一執(zhí)行效率高于方式二

[!結(jié)論]
當(dāng)過(guò)濾條件中有聚合函數(shù)時(shí)，則此過(guò)濾條件必須聲明HAVING中
當(dāng)過(guò)濾條件中沒(méi)有聚合函數(shù)時(shí)，則此過(guò)濾條件在WHERE或HAVING 中都可以，但是建議聲明在WHERE中

[!WHERE和HAVING的對(duì)比]

HAVING的適用范圍更廣
若沒(méi)有聚合函數(shù)，WHERE的執(zhí)行效率比HAVING高(從下面的執(zhí)行原理中可見(jiàn)一斑)

4. SQL 底層執(zhí)行原理

4.1 SELECT 語(yǔ)句的完整結(jié)構(gòu)

SQL92語(yǔ)法

SELECT ...,...,...(存在聚合函數(shù)）
FROM ...,...,...
WHERE 多表的連接條件 AND 不包含聚合函數(shù)的過(guò)濾條件
GROUP BY ...,...
HAVING 包含聚合函數(shù)的過(guò)濾條件
ORDER BY ...,...(ASC/DESC)
LIMIT ...,...

SQL99語(yǔ)法

SELECT ...,...,...(存在聚合函數(shù)）
FROM ...(LEFT/RIGHT)JOIN...ON 多表的連接條件
WHERE 不包含聚合函數(shù)的過(guò)濾條件
GROUP BY ...,...
HAVING 包含聚合函數(shù)的過(guò)濾條件
ORDER BY ...,...(ASC/DESC)
LIMIT ...,...

4.2 SELECT執(zhí)行順序

FROM -> ON -> (LEFT/RIGHT)JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

在 SELECT 語(yǔ)句執(zhí)行這些步驟的時(shí)候，每個(gè)步驟都會(huì)產(chǎn)生一個(gè)虛擬表 ，然后將這個(gè)虛擬表傳入下一個(gè)步驟中作為輸入
這些步驟隱含在 SQL 的執(zhí)行過(guò)程中，對(duì)于我們來(lái)說(shuō)是不可見(jiàn)的

4.3 SQL 的執(zhí)行原理

SQL 是聲明式語(yǔ)言，我們寫(xiě)的是“要什么”，數(shù)據(jù)庫(kù)引擎負(fù)責(zé)“怎么實(shí)現(xiàn)”。其底層執(zhí)行遵循固定的邏輯順序（≠ 書(shū)寫(xiě)順序），該順序決定了查詢(xún)?nèi)绾沃鸩缴山Y(jié)果

階段	關(guān)鍵字/操作	作用	輸出虛擬表
1. FROM + JOIN	`FROM` + `JOIN`（含 `CROSS JOIN`, `INNER JOIN`, `LEFT JOIN` 等）	構(gòu)建初始數(shù)據(jù)集： • 多表時(shí)先做笛卡爾積（`CROSS JOIN`）→ 得 `vt1-1` • 再通過(guò) `ON` 條件篩選 → `vt1-2` • 若有外連接（LEFT/RIGHT/FULL），添加外部行 → `vt1-3`	`vt1`（最終原始數(shù)據(jù)集）
2. WHERE	`WHERE`	對(duì) `vt1` 進(jìn)行行級(jí)過(guò)濾（條件篩選）	`vt2`
3. GROUP BY	`GROUP BY`	在 `vt2` 上按指定列分組	`vt3`（分組后中間表）
4. HAVING	`HAVING`	對(duì) `vt3` 的分組結(jié)果進(jìn)行過(guò)濾（聚合函數(shù)可用）	`vt4`
5. SELECT	`SELECT`	提取指定字段（可含表達(dá)式、別名）	`vt5-1`
6. DISTINCT	`DISTINCT`	去除重復(fù)行（在 `vt5-1` 上操作）	`vt5-2`
7. ORDER BY	`ORDER BY`	按指定字段排序（穩(wěn)定排序）	`vt6`
8. LIMIT / OFFSET	`LIMIT [n] [OFFSET m]`	截取前 n 行（或跳過(guò) m 行后取 n 行）	`vt7`（最終結(jié)果集）

虛擬表命名說(shuō)明：

vt1-x：FROM+JOIN 階段的子步驟
vt1～vt7：各主階段輸出的邏輯中間結(jié)果

補(bǔ)充：

并非所有階段都存在
- 若無(wú) GROUP BY，則跳過(guò) GROUP BY 和 HAVING
- 若無(wú) DISTINCT，則跳過(guò)該步
- 若無(wú) ORDER BY 或 LIMIT，對(duì)應(yīng)階段省略
關(guān)鍵字順序 ≠ 執(zhí)行順序
- 書(shū)寫(xiě)順序：SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT
- 執(zhí)行順序：FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT
為什么理解執(zhí)行順序很重要？
- 解釋為何 WHERE 中不能用 SELECT 別名（此時(shí)尚未執(zhí)行 SELECT）
- 理解 HAVING 可用聚合函數(shù)而 WHERE 不行（分組未完成）
- 優(yōu)化查詢(xún)：避免在 WHERE 中使用函數(shù)導(dǎo)致索引失效（因 WHERE 早于 SELECT）