第5讲SQL数据分组查询..docVIP

第5讲 数据分组查询 分组函数 分组函数用于统计表的数据，作用于多行，并返回一个统计结果。分组函数又称多行函数。 使用范围： （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：统计公司工资在1100～3000元之间的雇员人数 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