武穴市住房和城乡建设局网站,手机网站绑定域名是什么,网站官网建设的价格,广告主资源哪里找GROUP BY 和 HAVING 子句#xff1a;分组与过滤
在 Oracle 11g 中#xff0c;GROUP BY 子句用于根据一个或多个列对查询结果进行分组#xff0c;而 HAVING 子句用于对分组后的结果进行过滤。这两者常结合聚合函数使用#xff0c;用以实现复杂的数据统计和分析。本文将系统…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 中用于分组统计和结果过滤的关键工具。通过灵活使用它们可以实现各种复杂的数据分析需求。掌握其语法和高级用法将为数据库查询与分析奠定坚实的基础。
