SQL 聚合函数与分组查询

SQL 聚合函数与分组查询学习笔记

聚合函数和分组查询是 SQL 中用于对数据进行汇总和分析的重要工具。通过聚合函数，可以对数据进行求和、求平均值、统计记录数等操作；通过分组查询，可以将数据按指定列分组，并对每组数据进行聚合计算。

1. 聚合函数

聚合函数用于对一组值进行计算，并返回单个值。常用的聚合函数包括：

（1）SUM：求和

-- 求所有员工的工资总和
SELECT SUM(sal) FROM tmp_emp;

（2）MAX：求最大值

-- 求最高工资
SELECT MAX(sal) FROM tmp_emp;

（3）MIN：求最小值

-- 求最低工资
SELECT MIN(sal) FROM tmp_emp;

（4）AVG：求平均值

-- 求所有员工的平均工资
SELECT AVG(sal) FROM tmp_emp;

（5）COUNT：统计记录数

-- 统计有多少人有工资
SELECT COUNT(sal) FROM tmp_emp;

-- 统计总人数
SELECT COUNT(*) FROM tmp_emp;

-- 统计去除重复工资后的员工数
SELECT COUNT(DISTINCT sal) FROM tmp_emp;

注意：

• COUNT(列名) 忽略 NULL 值。
• COUNT(*) 统计所有行，不忽略 NULL 值。

2. 分组查询

分组查询通过 GROUP BY 子句将数据按指定列分组，并对每组数据进行聚合计算。

示例：

-- 按部门编号分组，统计每个部门的平均工资
SELECT deptno, AVG(sal)
FROM tmp_emp
WHERE deptno IS NOT NULL
GROUP BY deptno;

-- 按部门编号和工资分组
SELECT deptno, sal
FROM tmp_emp
GROUP BY deptno, sal;

注意：

• GROUP BY 子句中的列必须是查询中的列或聚合函数。
• WHERE 子句在分组前过滤数据，HAVING 子句在分组后过滤数据。

3. HAVING 子句

HAVING 子句用于对分组后的结果进行过滤。

示例：

-- 显示平均工资大于 2000 的部门编号和平均工资
SELECT deptno, AVG(sal)
FROM tmp_emp
GROUP BY deptno
HAVING AVG(sal) > 2000;

-- 显示平均工资大于 2000 的部门编号和平均工资，但不包括 10 部门
SELECT deptno, AVG(sal)
FROM tmp_emp
WHERE deptno <> 10
GROUP BY deptno
HAVING AVG(sal) > 2000;

注意：

• HAVING 子句只能用于分组后的过滤，不能用于分组前的过滤。

4. 综合示例

以下是一些综合示例，展示了聚合函数和分组查询的实际应用。

（1）统计每个部门的年薪

-- 统计每个部门的年薪
SELECT deptno, AVG(sal + NVL(comm, 0)) * 12 AS 年薪
FROM tmp_emp
WHERE deptno IS NOT NULL
GROUP BY deptno;

（2）查询员工的记录数、工资总和、平均工资、最高工资、最低工资

-- 查询员工的记录数、工资总和、平均工资、最高工资、最低工资
SELECT COUNT(*), SUM(sal), AVG(sal), MAX(sal), MIN(sal)
FROM tmp_emp;

（3）显示非办事人员（CLERK）的工作名称及月工资总和

-- 显示非办事人员的工作名称及月工资总和
SELECT job, SUM(sal)
FROM tmp_emp
WHERE job <> 'CLERK' AND sal < 5000
GROUP BY job
ORDER BY SUM(sal) ASC;

5. 高级分组查询

Oracle 提供了 ROLLUP 和 CUBE 函数，用于生成多层次的分组汇总结果。

（1）ROLLUP：生成分组汇总和小计

-- 统计每个部门的工资总和，并生成总汇总
SELECT deptno, SUM(sal)
FROM tmp_emp
GROUP BY ROLLUP(deptno);

-- 统计每个部门和职位的工资总和，并生成部门小计和总汇总
SELECT deptno, job, SUM(sal), GROUPING(deptno)
FROM tmp_emp
GROUP BY ROLLUP(deptno, job);

（2）CUBE：生成所有可能的分组汇总

-- 统计每个部门和职位的工资总和，并生成所有可能的分组汇总
SELECT deptno, job, SUM(sal), GROUPING(deptno)
FROM tmp_emp
WHERE deptno IS NOT NULL
GROUP BY CUBE(deptno, job);

6. 找出重复的行

通过分组和 HAVING 子句，可以找出表中重复的行。

示例：

-- 创建表 A 并插入数据
CREATE TABLE A AS SELECT * FROM tmp_emp WHERE 1 <> 1;
INSERT INTO A(empno, ename) VALUES(9999, 'AAAA');
INSERT INTO A SELECT * FROM tmp_emp WHERE empno = 6666;

-- 找出 empno 重复的行
SELECT empno, COUNT(empno)
FROM A
GROUP BY empno
HAVING COUNT(empno) >= 2;

7. 练习

以下是一些练习题及其解答。

练习 1

查询编号为 20、30 部门的平均工资，并将平均工资大于 2000 的输出，按平均工资排序

SELECT AVG(sal)
FROM tmp_emp
WHERE deptno IN (20, 30)
GROUP BY deptno
HAVING AVG(sal) > 2000
ORDER BY 1;

练习2

显示非办事人员（CLERK）工作名称，以及从事同一工作雇员的月工资的总和，并且要满足雇员的月工资小于5000，输出结果按月工资 ，合计升序排序

SELECT job, SUM(sal)
FROM tmp_emp
WHERE job <> 'CLERK' AND sal < 5000
GROUP BY job
ORDER BY 2 ASC;

总结

1. 聚合函数：SUM、MAX、MIN、AVG、COUNT。
2. 分组查询：GROUP BY 和 HAVING。
3. 高级分组查询：ROLLUP 和 CUBE。
4. 找出重复行：通过分组和 HAVING 子句实现。