第5讲SQL数据分组查询

第 5 讲 数据分组查询 1、分组函数 分组函数用于统计表的数据，作用于多行，并返回一个统计结果。分组函数又称多行函数。使用范围：（1）分组函数只能出现在选择列、order by 子句、having 子句中（2）当使用分组函数时，会忽略 null 行（3）如果在选择列表中既包含分组函数，也包含了其它列和表达式，那么这些列和表达式必须出现在 group by 子句中。（4）当使用分组函数时，可以在函数中指定 all 和 distinct 选项。其中 all 是默认选项，该选项表示统计所有行数据（包含重复值）；如果指定 distinct，则只会统计不同行值。1.1 Max、Min、Avg 和 Sum Max 函数：取列或表达式最大值；Min 函数：取列或表达式最小值；Avg 函数：取得列或表达式平均值；Sum 函数：取得列或表达式总和。例：查询雇员的最高工资、最低工资、平均工资以及工资总额。SQL select max(sal),min(sal),avg(sal),sum(sal)from emp;MAX(SAL)MIN(SAL)AVG(SAL)SUM(SAL)-5000 800 2073.21428 29025 练习 1：查询从事销售工作的雇员最高工资、最低工资、平均工资以及工资总额。SQL select max(sal),min(sal),avg(sal),sum(sal)from emp where job like%SALES%;MAX(SAL)MIN(SAL)AVG(SAL)SUM(SAL)-1600 1250 1400 5600 练习 2：查询部门编号为 30 的部门雇员最高工资、最低工资、平均工资以及工资总额。练习 3：计算能获得奖金的雇员平均奖。练习 4：计算公司所有雇员的平均奖。（组函数中使用 nvl 函数）SQL select avg(nvl(comm,0)as 所有雇员平均奖 from emp;所有雇员平均奖-157.1428571428 1.2 count Count 函数：取得满足条件的记录总行数。例 1：统计公司雇员总人数。SQL select count(*)as 雇员总人数 from emp;雇员总人数-14 例 2：统计 30 号部门能挣得奖金的雇员总人数 SQL select count(comm)as 能挣得奖金人数 from emp where deptno=30;能挣得奖金人数-4 练习 1：统计在 30 号部门工作的雇员人数 练习 2：统计公司工作为“MANAGER”的雇员人数 练习 3：统计公司工资在 11003000元之间的雇员人数 2、Group by 和 having 子句 Group by 子句用于对查询结果分组统计；having 子句用于限制分组显示结果 2.1 使用 group by 进行单列分组 基于单列生成分组统计结果，为分组列的每个不同值分别生成统计结果 例：查询每个部门的平均工资，按部门编号升序排列 SQL select deptno,avg(sal)from emp group by deptno order by deptno;DEPTNO AVG(SAL)-10 2916.66666 20 2175 30 1566.66666 练习：查询每个岗位的平均工资，按计算结果排序 2.2 使用 group by 进行多列分组 基于两个或两个以上的列生成分组统计结果。例：查询每个部门每种岗位的最低工资、最高工资和平均工资，按部门编号排序。SQL select deptno,job,min(sal),max(sal),avg(sal)from emp group by deptno,job order by deptno;DEPTNO JOB MIN(SAL)MAX(SAL)AVG(SAL)-10 CLERK 1300 1300 1300 10 MANAGER 2450 2450 2450 10 PRESIDENT 5000 5000 5000 20 ANALYST 3000 3000 3000 20 CLERK 800 1100 950 20 MANAGER 2975 2975 2975 30 CLERK 950 950 950 30 MANAGER 2850 2850 2850 30 SALESMAN 1250 1600 1400 思考？group by 子句中是否可以漏写 job 字段。练习：按所在部门及从事工作分组统计雇员一年内薪水总和，按部门编号排序。SQL select deptno as 部门编号,job as 工作,sum(12*(sal+nvl(comm,0)as 薪水总和 from emp group by deptno,job order by deptno;部门号 工作 薪水总和-10 CLERK 15600 10 MANAGER 29400 10 PRESIDENT 60000 20 ANALYST 72000 20 CLERK 22800 20 MANAGER 35700 30 CLERK 11400 30 MANAGER 34200 30 SALESMAN 93600 2.3 使用 having 子句限制分组显示结果 Having 子句必须出现在 group by 子句后面。例：按部门显示平均工资低于 2500 的部门号、平均工资和最高工资。SQL select deptno as 部门编号,avg(sal)as 平均工资,max(sal)as 最高工资 from emp group by deptno having avg(sal)select deptno as 部门编号,avg(sal)as 平均工资,max(sal)as 最高工资 from emp where avg(sal)select deptno as 部门编号,max(sal)as 最高工资 from emp group by deptno having max(sal)2900;练习2：显示非销售雇员工作名称以及从事同一工作雇员的月工资总和，并且满足从事同一工作雇员的月工资总和大于5000，输出结果按月工资总和升序排序。SQL select job as 工作,sum(sal)as 月工资总和 from emp where job not like%SALES%group by job having sum(sal)5000 order by sum(sal);工作 月工资总和-ANALYST 6000 MANAGER 8275 2.4 分组函数嵌套 例：显示各部门平均工资最低值和最高值。SQL select min(avg(sal)平均工资最低值,max(avg(sal)平均工资最高值 from emp group by deptno;平均工资最低值 平均工资最高值-1566.666666666 2916.666666666 练习：按雇员从事的工作显示平均工资最低值和最高值。