第5讲 分组查询 IBM Confidential.

Presentation on theme: "第5讲 分组查询 IBM Confidential."— Presentation transcript:

1 第5讲 分组查询 IBM Confidential

2 目标 分组函数 分组查询

3 分组函数

4 分组函数 分组函数 注意事项 分组函数对一组数据进行运算，针对一组数据（多行记录）只返回一个结果，也称多行函数或者分聚合函数。
1、聚合函数不能和普通列一起使用 2、聚合函数经常用在分组查询中 函数名 功能说明 适用类型 avg() 计算平均值 数值类型 sum() 求和 count() 计算查询得到的记录行数 任何数据类型 max() 计算最大值 min() 计算最小值

5 分组函数的使用 用于数值类型 select avg(sal), sum(sal), count(sal), max(sal), min(sal) from emp; 用于日期类型 select count(hiredate), max(hiredate), min(hiredate) from emp; 用于字符类型 select count(ename),max(ename), min(ename) from emp; COUNT()函数使用 1、count(*)返回组中总记录数目； 2、count(exp)返回表达式exp值非空的记录数目； 3、count(distinct(exp))返回表达式exp值不重复的、非空的记录数目。 select count(*) from emp; select count(comm) from emp; select count(distinct(deptno)) from emp;

6 分组函数的使用 分组函数省略列中的空值 select avg(comm) from emp;
用于空值类型 分组函数省略列中的空值 select avg(comm) from emp; select sum(comm) from emp; 可使用NVL()函数强制分组函数处理空值 select avg(nvl(comm, 0)) from emp; 错误使用 分组函数不能和普通列同在select语句中使用，因为分组函数查询返回的是单一值，普通列查询返回的是多个值。 select count(empno),ename from scott.emp;

7 分组查询

8 分组查询语法 SELECT column, group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING groupby_condition] [ORDER BY column| group_function(column)];

9 查询雇员表，统计各部门雇员的平均工资。怎么办？
单列分组汇总 查询雇员表，统计各部门雇员的平均工资。怎么办？ SELECT deptno,avg(sal) FROM scott.emp GROUP BY deptno

10 查询雇员表，统计各部门中各岗位雇员的平均工资。怎么办？
多列分组汇总 查询雇员表，统计各部门中各岗位雇员的平均工资。怎么办？ SELECT deptno,job,avg(sal) FROM scott.emp GROUP BY deptno,job

11 查询雇员表，统计各部门中各岗位雇员的平均工资大于2000。怎么办？
分组过滤 查询雇员表，统计各部门中各岗位雇员的平均工资大于2000。怎么办？ SELECT deptno,job,avg(sal) FROM scott.emp GROUP BY deptno,job HAVING avg(sal)>2000

12 查询雇员表，统计各部门中各岗位雇员的平均工资大于2000，按平均工资的高低进行排序。怎么办？
分组排序 查询雇员表，统计各部门中各岗位雇员的平均工资大于2000，按平均工资的高低进行排序。怎么办？ SELECT deptno,job,avg(sal) FROM scott.emp GROUP BY deptno,job HAVING avg(sal)>2000 ORDER BY avg(sal);

13 分组查询注意事项 分组表 分组表中的合法列 分组查询中的查询列 为分组查询后产生的新表，表中被分组的字段值不会存在重复值 1、被分组的列
2、对原表的列进行计算并返回单一值的聚合函数表达式 分组查询中的查询列 是查询生成分组表的操作，所以Select之后的合法列只能是分组表中的合法列 不允许在WHERE 子句中使用分组函数。WHERE是分组前的过滤 非法分组查询 SELECT ename,deptno,avg(sal) FROM scott.emp WHERE avg(sal)>2000 GROUP BY deptno

14 分组查询执行次序 WHERE GROUP BY 聚合函数 HAVING ORDER BY 操作原始表：对原始表的记录进行过滤筛选
操作原始表：将分组字段在原始表中的值原版的搬送到分组表中 聚合函数 操作原始表：聚合函数对各组值进行汇总后搬送到分组表中 HAVING 操作分组表：对分组表中的记录进行过滤筛选 ORDER BY 操作分组表：对分组表中的记录进行排序

15 分组查询执行次序举例 SELECT deptno,job,avg(sal) FROM scott.emp
WHERE ename<>'scott' GROUP BY deptno,job HAVING avg(sal)>2000 ORDER BY avg(sal) desc

16 小结1 以下的查询语句： Select * From A 将得到以下的查询结果： A1 B1 C1 1001 P1 70
那么，以下的查询语句将输出哪些信息？ Select A1,B1,MAX(C1) FROM A