MySQL 高級特性：集合函數（COUNT、AVG、SUM、MAX、MIN）與 GROUP BY 實戰詳解

MySQL 集合函數（聚合函數）對行集合進行統計計算，常與 GROUP BY 配合實現分組統計。本文詳解五大核心函數語法、DISTINCT 去重、NULL 忽略規則、分組查詢陷阱與實戰範例。

一、五大核心集合函數

二、COUNT 進階用法

1. 基礎計數

SELECT COUNT(*) FROM pet WHERE species='cat';  -- 2
SELECT COUNT(species) FROM pet;                -- 9（含重複）
SELECT COUNT(DISTINCT species) FROM pet;       -- 5（去重）

2. 分組統計

SELECT species, COUNT(*) FROM pet GROUP BY species;
+---------+----------+
| species | COUNT(*) |
+---------+----------+
| bird    | 2        |
| cat     | 2        |
| dog     | 3        |
| hamster | 1        |
| snake   | 1        |
+---------+----------+

三、AVG 平均值實戰

-- 站點投票平均分（1-10）
SELECT AVG(vote) FROM opinion;

-- 寵物品種平均年齡（天）
SELECT species, AVG(DATEDIFF(CURDATE(), birth)) FROM pet GROUP BY species;
+---------+----------------------------------+
| species | AVG(DATEDIFF(CURDATE(), birth))  |
+---------+----------------------------------+
| bird    | 34160                            |
| cat     | 74959.5                          |
| dog     | 112829.66666667                  |
| hamster | 19890                            |
| snake   | 49791                            |
+---------+----------+

四、SUM 求和實戰

-- 訂單總金額
SELECT SUM(purchase_amount) FROM orders;

-- 同品種寵物年齡總和（對比平均值）
SELECT species, SUM(DATEDIFF(CURDATE(), birth)) FROM pet GROUP BY species;
+---------+----------------------------------+
| species | SUM(DATEDIFF(CURDATE(), birth))  |
+---------+----------------------------------+
| bird    | 68320                            |
| cat     | 149919                           |
| dog     | 338489                           |
| hamster | 19890                            |
| snake   | 49791                            |
+---------+----------+

五、MAX/MIN 極值實戰

1. 單表極值

SELECT MIN(birth) FROM pet;  -- 1989-05-13（最早出生）

2. 陷阱：非分組列無法直接搭配極值

-- 錯誤：name 非分組列
SELECT name, MIN(birth) FROM pet;  -- ERROR 1140

3. 正確：分組極值

SELECT species, MIN(birth) FROM pet GROUP BY species;
+---------+------------+
| species | MIN(birth) |
+---------+------------+
| bird    | 1997-12-09 |
| cat     | 1993-02-04 |
| dog     | 1989-05-13 |
| hamster | 1999-03-30 |
| snake   | 1996-04-29 |
+---------+------------+

SELECT species, MAX(birth) FROM pet GROUP BY species;
+---------+------------+
| species | MAX(birth) |
+---------+------------+
| bird    | 1998-09-11 |
| cat     | 1994-03-17 |
| dog     | 1990-08-31 |
| hamster | 1999-03-30 |
| snake   | 1996-04-29 |
+---------+------------+

六、核心規則總結

1. 集合函數只返回單值（標量），不可與非分組列直接混用
2. GROUP BY 列 可直接出現在 SELECT 列表，**非 GROUP BY 列必須包裹在集合函數中**
3. NULL 值被所有集合函數忽略**（COUNT(*) 除外）
4. DISTINCT 可用於 COUNT、SUM、AVG** 去重計算
5. WHERE 過濾在分組前，HAVING 過濾在分組後**（聚合條件用 HAVING）