案例：分组聚合

我为你设计一道经典的员工薪资统计分组聚合练习题，贴合实际业务场景，覆盖 GROUP BY 核心用法+常用聚合函数（COUNT()/SUM()/AVG()/MAX()/MIN()），还包含新手易混淆的 WHERE（分组前筛选）和 HAVING（分组后筛选）的区别，步骤分为「创建表」「填充数据」「分层练习题」（从简单到复杂），你可直接复制SQL运行，快速掌握MySQL分组聚合查询。

一、练习题场景说明

场景：某公司的员工信息表（emp），包含「员工ID、姓名、部门、职位、薪资、入职日期」字段，需要通过分组聚合完成各类薪资/人员统计需求，覆盖分组聚合的核心使用场景。

---

二、步骤1：创建员工表（emp）

复制以下SQL直接运行，创建符合场景的员工表，字段类型贴合实际业务需求：

CREATE TABLE emp (
    emp_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '员工唯一ID',
    emp_name VARCHAR(20) NOT NULL COMMENT '员工姓名',
    dept VARCHAR(30) NOT NULL COMMENT '部门名称（可分组）',
    job VARCHAR(30) NOT NULL COMMENT '职位（可分组）',
    salary DECIMAL(10, 2) NOT NULL COMMENT '月薪（可聚合计算）',
    hire_date DATE NOT NULL COMMENT '入职日期（可筛选）'
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT '公司员工信息表';

---

三、步骤2：填充测试数据

复制以下SQL批量插入12条员工数据，覆盖「4个部门、5种职位」，数据有差异，保证聚合结果有参考性：

INSERT INTO emp (emp_name, dept, job, salary, hire_date)
VALUES
('张三', '技术部', '后端开发', 20000.00, '2020-01-15'),
('李四', '技术部', '前端开发', 18000.00, '2020-03-20'),
('王五', '技术部', '后端开发', 25000.00, '2019-05-10'),
('赵六', '技术部', '测试工程师', 15000.00, '2021-07-08'),
('钱七', '市场部', '销售', 12000.00, '2022-02-25'),
('孙八', '市场部', '销售', 18000.00, '2021-09-30'),
('周九', '市场部', '产品经理', 22000.00, '2020-11-12'),
('吴十', '人事部', 'HR', 10000.00, '2023-01-05'),
('郑十一', '人事部', 'HR', 12000.00, '2022-08-18'),
('王十二', '财务部', '会计', 15000.00, '2021-04-20'),
('冯十三', '财务部', '出纳', 8000.00, '2023-03-10'),
('陈十四', '财务部', '财务主管', 25000.00, '2019-08-01');

插入完成后，可先执行 SELECT * FROM emp; 查看完整数据，确认数据无误。

---

四、步骤3：分层聚合查询练习题（从简单到复杂）

核心前置知识

1. 聚合函数：COUNT()（计数）、SUM()（求和）、AVG()（平均值）、MAX()（最大值）、MIN()（最小值），仅对「非NULL值」计算（本题薪资无NULL，可直接使用）。
2. GROUP BY：按指定字段分组，分组字段需与查询字段对应（非聚合字段必须出现在 GROUP BY 中）。
3. 筛选区别：WHERE（分组前筛选数据，不能使用聚合函数）、HAVING（分组后筛选聚合结果，可使用聚合函数）。

---

题1：简单分组 + 基础聚合（入门）

查询目标

按「部门」分组，统计每个部门的：① 员工人数 ② 总薪资 ③ 平均薪资 ④ 最高薪资 ⑤ 最低薪资（平均薪资保留2位小数）。

SQL语句

SELECT
    dept AS '部门',
    COUNT(emp_id) AS '员工人数',  -- 计数：用员工ID更准确（无NULL）
    SUM(salary) AS '部门总薪资',
    ROUND(AVG(salary), 2) AS '部门平均薪资',  -- ROUND() 保留2位小数，优化展示
    MAX(salary) AS '部门最高薪资',
    MIN(salary) AS '部门最低薪资'
FROM emp
GROUP BY dept;  -- 按部门分组（非聚合字段dept必须出现在GROUP BY中）

预期结果（核心逻辑）

• 技术部4人，总薪资78000.00，平均19500.00；
• 财务部3人，总薪资48000.00，平均16000.00；
• 分组结果按部门名称默认排序（可加 ORDER BY 员工人数 DESC 按人数倒序）。

---

题2：分组前筛选（WHERE） + 聚合（进阶）

查询目标

先筛选出「2021年及以后入职」的员工，再按「部门」分组，统计每个部门的员工人数和平均薪资。

关键解析

• 筛选条件「2021年及以后入职」是分组前对原始数据的筛选，用 WHERE；
• 日期筛选：hire_date >= '2021-01-01'（MySQL支持直接比较日期字符串）。

SQL语句

SELECT
    dept AS '部门',
    COUNT(emp_id) AS '2021年后入职人数',
    ROUND(AVG(salary), 2) AS '2021年后入职平均薪资'
FROM emp
WHERE hire_date >= '2021-01-01'  -- 分组前筛选：仅保留2021年及以后入职的员工
GROUP BY dept;

预期结果（核心逻辑）

• 技术部仅1人（赵六，2021-07-08入职），平均薪资15000.00；
• 人事部2人（均2022/2023入职），平均薪资11000.00；
• 财务部2人（冯十三2023、王十二2021），平均薪资11500.00。

---

题3：分组后筛选（HAVING） + 聚合（重点，新手易混淆）

查询目标

按「职位」分组，统计每个职位的平均薪资，仅保留平均薪资≥15000的职位（平均薪资保留2位小数）。

关键解析

• 筛选条件「平均薪资≥15000」是分组后对聚合结果的筛选，用 HAVING，且可直接使用聚合函数别名；
• 不可用 WHERE 筛选聚合结果（WHERE 不支持聚合函数）。

SQL语句

SELECT
    job AS '职位',
    ROUND(AVG(salary), 2) AS '职位平均薪资'
FROM emp
GROUP BY job
HAVING 职位平均薪资 >= 15000;  -- 分组后筛选：仅保留平均薪资≥15000的职位（也可写 AVG(salary) >= 15000）

预期结果（核心逻辑）

• 后端开发、前端开发、产品经理、财务主管会被保留（平均薪资均≥15000）；
• 销售、HR、会计、出纳会被过滤（平均薪资<15000）。

---

题4：多字段分组（复杂场景，实际开发常用）

查询目标

按「部门+职位」双字段分组，统计每个部门下每个职位的：① 员工人数 ② 该职位的总薪资（即「部门-职位」组合的聚合统计）。

关键解析

• 多字段分组：GROUP BY 字段1, 字段2，分组优先级「字段1 > 字段2」（先按部门分，再按职位分）；
• 非聚合字段「dept、job」均需出现在 GROUP BY 中。

SQL语句

SELECT
    dept AS '部门',
    job AS '职位',
    COUNT(emp_id) AS '该职位人数',
    SUM(salary) AS '该职位总薪资'
FROM emp
GROUP BY dept, job;  -- 多字段分组：先部门，后职位

预期结果（核心逻辑）

• 技术部-后端开发2人，总薪资45000.00；
• 市场部-销售2人，总薪资30000.00；
• 每个「部门-职位」组合为一条记录，无重复。

---

题5：WHERE+GROUP BY+HAVING+ORDER BY 综合运用（实战）

查询目标

1. 先筛选出「薪资>10000且2020年及以后入职」的员工；
2. 按「部门」分组，统计每个部门的员工人数和平均薪资（保留2位小数）；
3. 仅保留「员工人数≥2」的部门；
4. 最终按「平均薪资」倒序排序。

关键解析

• 执行顺序：WHERE → GROUP BY → HAVING → ORDER BY；
• 全流程覆盖：筛选→分组→聚合→分组后筛选→排序，贴合实际开发场景。

SQL语句

SELECT
    dept AS '部门',
    COUNT(emp_id) AS '符合条件人数',
    ROUND(AVG(salary), 2) AS '符合条件平均薪资'
FROM emp
WHERE salary > 10000 AND hire_date >= '2020-01-01'  -- 分组前筛选：薪资>10000+2020年后入职
GROUP BY dept
HAVING 符合条件人数 >= 2  -- 分组后筛选：人数≥2
ORDER BY 符合条件平均薪资 DESC;  -- 按平均薪资倒序排序

预期结果（核心逻辑）

• 技术部3人（张三、李四、赵六），平均薪资17666.67，排第1；
• 市场部3人（钱七、孙八、周九），平均薪资17333.33，排第2；
• 人事部2人，平均薪资11000.00，排第3；
• 财务部1人（王十二），被 HAVING 过滤，不显示。

---

五、核心知识点总结

1. 分组聚合核心语法：SELECT 分组字段, 聚合函数 FROM 表 GROUP BY 分组字段，非聚合字段必须出现在 GROUP BY 中。
2. 常用聚合函数：COUNT()（计数）、SUM()（求和）、AVG()（平均值）、MAX()（最大值）、MIN()（最小值），可搭配 ROUND() 优化数值展示。
3. 筛选与排序执行顺序：WHERE（分组前，不支持聚合函数）→ GROUP BY（分组）→ HAVING（分组后，支持聚合函数）→ ORDER BY（最终排序）。
4. 多字段分组：GROUP BY 字段1, 字段2，按字段顺序逐级分组，贴合复杂业务统计需求。

通过这道练习题，你可全面掌握MySQL分组聚合的核心用法，后续只需替换表和字段，即可适配实际工作中的各类统计场景。