Oracle GROUP BY 基础教程

Oracle GROUP BY 基础教程一、GROUP BY 是什么GROUP BY​ 是 SQL 中用于将结果集按一个或多个列进行分组的子句通常与聚合函数如COUNT、SUM、AVG等配合使用用于对分组后的数据进行统计分析。核心作用将相同分组条件的行“合并”为一组并对每组计算汇总结果。二、基本语法结构SELECT 分组列, 聚合函数(列) FROM 表名 [WHERE 筛选条件] GROUP BY 分组列 [HAVING 分组筛选条件] [ORDER BY 排序列];执行顺序FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY三、核心概念与示例以员工表EMP为例字段empno,ename,job,sal,deptno-- 表结构参考 CREATE TABLE emp ( empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), sal NUMBER(7,2), deptno NUMBER(2) );1. 单字段分组需求统计每个部门的员工人数。SELECT deptno, COUNT(*) AS 员工人数 FROM emp GROUP BY deptno;结果DEPTNO员工人数1032053062. 多字段分组需求统计每个部门、每种岗位的员工人数。SELECT deptno, job, COUNT(*) AS 员工人数 FROM emp GROUP BY deptno, job ORDER BY deptno, job;关键点GROUP BY deptno, job表示先按deptno分组再按job分组嵌套分组。3. 常用聚合函数函数作用示例COUNT(*)统计行数含NULLCOUNT(*)COUNT(列)统计非NULL值行数COUNT(comm)SUM(列)求和SUM(sal)AVG(列)求平均值AVG(sal)MAX(列)最大值MAX(sal)MIN(列)最小值MIN(hiredate)示例统计各部门工资总和、平均工资、最高/最低工资SELECT deptno, SUM(sal) AS 总工资, ROUND(AVG(sal), 2) AS 平均工资, MAX(sal) AS 最高工资, MIN(sal) AS 最低工资 FROM emp GROUP BY deptno;4. WHERE 与 HAVING 的区别子句作用执行时机能否用聚合函数WHERE筛选行分组前过滤GROUP BY前❌ 不能HAVING筛选组分组后过滤GROUP BY后✅ 能示例1统计工资大于2000的员工所在的部门人数WHERESELECT deptno, COUNT(*) AS 人数 FROM emp WHERE sal 2000 -- 先过滤工资2000的行 GROUP BY deptno;示例2统计各部门人数只显示人数大于3的部门HAVINGSELECT deptno, COUNT(*) AS 人数 FROM emp GROUP BY deptno HAVING COUNT(*) 3; -- 再过滤分组后的结果组合使用-- 统计各部门中工资2000的员工人数只显示人数2的部门 SELECT deptno, COUNT(*) AS 人数 FROM emp WHERE sal 2000 GROUP BY deptno HAVING COUNT(*) 2;四、重要规则与陷阱1. SELECT 后的列必须满足“分组列或聚合函数”错误写法Oracle会报错SELECT deptno, ename, COUNT(*) -- ename 既不在GROUP BY中也不是聚合函数 FROM emp GROUP BY deptno;正确写法-- 写法1ename加入分组 SELECT deptno, ename, COUNT(*) FROM emp GROUP BY deptno, ename; -- 写法2对ename使用聚合函数如取最大姓名 SELECT deptno, MAX(ename) AS 员工姓名, COUNT(*) FROM emp GROUP BY deptno;2. 分组列中的 NULL 值GROUP BY会将所有NULL值归为同一组。-- 假设comm列有NULL值统计各奖金值的员工数 SELECT comm, COUNT(*) FROM emp GROUP BY comm; -- 结果中会有一行comm为NULL表示该组是奖金为空的员工3. ORDER BY 排序默认按GROUP BY列升序排列可显式指定SELECT deptno, COUNT(*) AS cnt FROM emp GROUP BY deptno ORDER BY cnt DESC; -- 按人数降序排列五、高级用法1. 使用别名Oracle特性Oracle 允许在GROUP BY中使用列别名部分数据库不支持SELECT deptno AS 部门编号, COUNT(*) AS cnt FROM emp GROUP BY 部门编号 -- 使用别名 ORDER BY cnt;2. 与 DISTINCT 的区别DISTINCT仅去重GROUP BY可配合聚合函数统计。-- 查询有哪些部门去重 SELECT DISTINCT deptno FROM emp; -- 统计各部门人数分组统计 SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;3. ROLLUP/CUBE多维统计ROLLUP生成分组的小计和总计层次化统计。CUBE生成所有可能的分组组合交叉统计。示例ROLLUP统计各部门工资总和以及所有部门的总工资SELECT deptno, SUM(sal) AS 总工资 FROM emp GROUP BY ROLLUP(deptno);结果DEPTNO总工资1087502010875309400NULL290254. GROUPING SETS自定义分组-- 同时统计部门分组和岗位分组 SELECT deptno, job, SUM(sal) FROM emp GROUP BY GROUPING SETS ((deptno), (job));六、性能优化建议先用 WHERE 过滤减少分组的数据量WHERE 在 GROUP BY 前执行。避免 SELECT*只选择必要的列减少 I/O。合理使用索引对GROUP BY列建立索引可提升分组效率尤其数据量大时。慎用 HAVINGHAVING 是对分组后的结果过滤尽量用 WHERE 提前过滤。七、综合案例需求查询各部门中工资大于2000的员工人数只显示人数≥2的部门并按人数降序排列。SELECT deptno, COUNT(*) AS 人数, AVG(sal) AS 平均工资 FROM emp WHERE sal 2000 -- 先过滤工资2000的行 GROUP BY deptno HAVING COUNT(*) 2 -- 再过滤分组后的人数≥2的组 ORDER BY 人数 DESC; -- 最后按人数降序排列八、总结功能语法示例单字段分组GROUP BY deptno多字段分组GROUP BY deptno, job分组前过滤WHERE sal 2000分组后过滤HAVING COUNT(*) 3聚合统计COUNT(*),SUM(sal),AVG(sal)排序ORDER BY 聚合列 DESC