网站前台界面模板下载网易搜索引擎入口
GROUP BY 和 HAVING 子句：分组与过滤
在 Oracle 11g 中，GROUP BY 子句用于根据一个或多个列对查询结果进行分组，而 HAVING 子句用于对分组后的结果进行过滤。这两者常结合聚合函数使用，用以实现复杂的数据统计和分析。本文将系统讲解其语法、应用场景及实践案例。
一、GROUP BY 子句的基本语法
SELECT 列名1, 聚合函数(列名2), ...
FROM 表名
[WHERE 条件]
GROUP BY 列名1, 列名2, ...
[HAVING 条件];
说明：
GROUP BY：指定分组的列或表达式。- 聚合函数：如
SUM、COUNT、AVG、MAX、MIN等，用于对分组数据进行统计计算。 HAVING：用于过滤分组后的数据，与WHERE类似，但只能用于分组结果。
二、GROUP BY 的应用
1. 按单列分组
根据单个列的值对数据分组，并对每组数据应用聚合函数。
示例：计算每个部门的总薪资
SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;
结果： 每个部门一行，总薪资通过 SUM 函数计算得出。
2. 按多列分组
分组可以基于多个列的组合值。
示例：统计每个部门中不同职位的员工人数
SELECT department_id, job_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id, job_id;
结果： 每个部门中每种职位的员工数量。
3. 使用表达式分组
分组依据可以是计算结果或表达式。
示例：按薪资级别分组并统计员工人数
SELECT CASE WHEN salary >= 10000 THEN '高薪'WHEN salary >= 5000 THEN '中等薪资'ELSE '低薪'END AS salary_level, COUNT(*) AS employee_count
FROM employees
GROUP BY CASE WHEN salary >= 10000 THEN '高薪'WHEN salary >= 5000 THEN '中等薪资'ELSE '低薪'END;
结果： 返回按薪资级别分组后的员工数量。
三、HAVING 子句的应用
HAVING 子句用于对分组后的结果进行进一步过滤。它与 WHERE 的区别在于：
WHERE用于分组前过滤数据。HAVING用于分组后过滤分组结果。
1. HAVING 的基本使用
示例：筛选总薪资超过 50000 的部门
SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id
HAVING SUM(salary) > 50000;
说明： HAVING 条件中直接使用聚合函数进行筛选。
2. HAVING 与 WHERE 联合使用
示例：筛选出仅统计薪资大于 3000 的员工，并返回总薪资超过 20000 的部门
SELECT department_id, SUM(salary) AS total_salary
FROM employees
WHERE salary > 3000
GROUP BY department_id
HAVING SUM(salary) > 20000;
说明：
WHERE筛选薪资大于 3000 的员工，减少数据量。HAVING筛选分组后总薪资大于 20000 的部门。
3. HAVING 中的复杂条件
示例：筛选平均薪资大于 5000 且员工人数超过 5 的部门
SELECT department_id, AVG(salary) AS avg_salary, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 5000 AND COUNT(*) > 5;
说明： 使用多个条件组合对分组结果进行过滤。
四、GROUP BY 和 HAVING 的进阶用法
1. GROUP BY ROLLUP
ROLLUP 是一种扩展分组的功能，用于生成分组的汇总数据。
示例：统计每个部门的总薪资，并增加所有部门的总薪资行
SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY ROLLUP(department_id);
结果：
- 返回每个部门的总薪资。
- 额外增加一行显示所有部门的总薪资。
2. GROUP BY CUBE
CUBE 生成跨多个分组维度的汇总数据。
示例：统计每个部门和每种职位的薪资总额，同时增加汇总数据
SELECT department_id, job_id, SUM(salary) AS total_salary
FROM employees
GROUP BY CUBE(department_id, job_id);
结果：
- 每个部门和职位的薪资总额。
- 每个部门的汇总。
- 所有部门和职位的总汇总。
3. GROUPING 函数
GROUPING 用于判断当前行是否为汇总行，配合 ROLLUP 或 CUBE 使用。
示例：判断并标识汇总行
SELECT department_id, job_id, SUM(salary) AS total_salary,GROUPING(department_id) AS is_dept_summary,GROUPING(job_id) AS is_job_summary
FROM employees
GROUP BY CUBE(department_id, job_id);
说明：
- 如果
GROUPING(department_id)返回1，表示当前行是部门的汇总数据。
五、性能优化建议
-
减少不必要的分组列
- 只分组需要统计的列，减少资源开销。
-
适当使用索引
- 对分组列建立索引，优化查询性能。
-
谨慎使用复杂表达式
- 在分组表达式复杂时，可提前处理为中间结果表。
六、练习示例
- 统计每个部门的员工人数和平均薪资，并筛选出员工人数大于 5 且平均薪资超过 4000 的部门：
SELECT department_id, COUNT(*) AS employee_count, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5 AND AVG(salary) > 4000;
- 按部门和职位分组统计总薪资，输出汇总信息：
SELECT department_id, job_id, SUM(salary) AS total_salary
FROM employees
GROUP BY ROLLUP(department_id, job_id);
- 统计每种薪资级别的员工人数，并筛选人数超过 10 的薪资级别：
SELECT CASE WHEN salary >= 10000 THEN '高薪'WHEN salary >= 5000 THEN '中等薪资'ELSE '低薪'END AS salary_level,COUNT(*) AS employee_count
FROM employees
GROUP BY CASE WHEN salary >= 10000 THEN '高薪'WHEN salary >= 5000 THEN '中等薪资'ELSE '低薪'END
HAVING COUNT(*) > 10;
七、小结
GROUP BY 和 HAVING 子句是 SQL 中用于分组统计和结果过滤的关键工具。通过灵活使用它们，可以实现各种复杂的数据分析需求。掌握其语法和高级用法，将为数据库查询与分析奠定坚实的基础。