【Oracle11g SQL详解】GROUP BY 和 HAVING 子句：分组与过滤

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;