用group函数对数据进行分组统计.ppt

1、用group函数对数据进行分组统计,本章目的,完成本章之后，我们可以掌握以下的内容: 知道可用的group函数 描述 group 函数的使用 使用 GROUP BY 子句对数据分组 使用HAVING子句,什麽叫做 Group 函数?,Group 函数工作在行的集合上.,EMP,“EMP表里面的 最大工资”,DEPTNO SAL - - 10 2450 10 5000 10 1300 20 800 20 1100 20 3000 20 3000 20 2975 30 1600 30 2850 30 1250 30 950 30 1500 30 1250,MAX(SAL) - 5000,Group

2、函数的类型,AVG COUNT MAX MIN STDDEV SUM VARIANCE,使用Group 函数,SELECTcolumn, group_function(column) FROMtable WHEREcondition ORDER BYcolumn;,使用AVG 和SUM 函数,AVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL) - - - - 1400 1600 1250 5600,可以使用 AVG 和 SUM 对数字类型数据进行统计.,SQL SELECTAVG(sal), MAX(sal), 2MIN(sal), SUM(sal) 3FROMemp 4W

3、HEREjob LIKE SALES%;,使用MIN 和MAX 函数,可以在在任何数据类型上面使用 MIN 和 MAX函数,SQL SELECTMIN(hiredate), MAX(hiredate) 2 FROMemp;,MIN(HIRED MAX(HIRED - - 17-DEC-80 12-JAN-83,使用 COUNT 函数,COUNT(*) - 6,SQL SELECTCOUNT(*) 2 FROMemp 3 WHEREdeptno = 30;,COUNT(*) 返回table里面行的数量,Group函数与 Null 值,Group 函数忽视列里面的 null 值,SQL SELEC

4、T AVG(comm) 2 FROM emp;,AVG(COMM) - 550,在 Group函数里面使用NVL函数,NVL函数强迫group函数在统计时包含null值,SQL SELECT AVG(NVL(comm,0) 2 FROM emp;,AVG(NVL(COMM,0) - 157.14286,建立数据的分组,EMP,“每个部门的 平均工资”,DEPTNO SAL - - 10 2450 10 5000 10 1300 20 800 20 1100 20 3000 20 3000 20 2975 30 1600 30 2850 30 1250 30 950 30 1500 30 125

5、0,DEPTNO AVG(SAL) - - 10 2916.6667 20 2175 30 1566.6667,建立数据的分组 : GROUP BY 子句,SELECTcolumn, group_function(column) FROMtable WHEREcondition GROUP BYgroup_by_expression ORDER BYcolumn;,把table里面的数据分成几个小的组,使用 GROUP BY 子句,SQL SELECT deptno, AVG(sal) 2 FROM emp 3 GROUP BY deptno;,DEPTNO AVG(SAL) - - 10 2

6、916.6667 20 2175 30 1566.6667,使用 GROUP BY 子句,GROUP BY 列可以不在select的列表里面,SQL SELECT AVG(sal) 2 FROM emp 3 GROUP BY deptno;,AVG(SAL) - 2916.6667 2175 1566.6667,根据多个列进行分组,EMP,“根据每个工作求出 汇总工资，并按 部门分组”,DEPTNO JOB SAL - - - 10 MANAGER 2450 10 PRESIDENT 5000 10 CLERK 1300 20 CLERK 800 20 CLERK 1100 20 ANALYS

7、T 3000 20 ANALYST 3000 20 MANAGER 2975 30 SALESMAN 1600 30 MANAGER 2850 30 SALESMAN 1250 30 CLERK 950 30 SALESMAN 1500 30 SALESMAN 1250,JOB SUM(SAL) - - CLERK 1300 MANAGER 2450 PRESIDENT 5000 ANALYST 6000 CLERK 1900 MANAGER 2975 CLERK 950 MANAGER 2850 SALESMAN 5600,DEPTNO - 10 10 10 20 20 20 30 30 3

8、0,根据多个列进行分组,SQL SELECT deptno, job, sum(sal) 2 FROM emp 3 GROUP BY deptno, job;,DEPTNO JOB SUM(SAL) - - - 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 20 ANALYST 6000 20 CLERK 1900 . 9 rows selected.,使用 Group函数的非法查询,不能用where子句来限制group函数 可以用 HAVING 子句来限制 group.,SQL SELECT deptno, AVG(sal) 2 FROM e

9、mp 3 WHERE AVG(sal) 2000 4 GROUP BY deptno;,WHERE AVG(sal) 2000 * ERROR at line 3: ORA-00934: group function is not allowed here,不能用 WHERE 子句来限制group函数,对分组结果进行限制,“最高薪水超过2900 的部门”,EMP,DEPTNO SAL - - 10 2450 10 5000 10 1300 20 800 20 1100 20 3000 20 3000 20 2975 30 1600 30 2850 30 1250 30 950 30 1500

10、30 1250,DEPTNO MAX(SAL) - - 10 5000 20 3000,限制分组的结果: HAVING 子句,使用 HAVING 子句来限制分组 匹配HAVING子句的分组被显示,SELECTcolumn, group_function FROMtable WHEREcondition GROUP BYgroup_by_expression HAVINGgroup_condition ORDER BYcolumn;,使用HAVING 子句,SQL SELECT deptno, max(sal) 2 FROM emp 3 GROUP BY deptno 4 HAVING max(sal)2900;,DEPTNO MAX(SAL) - - 10 5000 20 3000,使用 HAVING 子句,SQL SELECT job, SUM(sal) PAYROLL 2 FROM emp 3 WHERE job NOT LIKE SALES% 4 GROUP BY job 5 HAVING SUM(sal)5000 6 ORDER BY SUM(sal);,JOB PAYROLL - - ANALYST 6000 MANAGER 8275,嵌套group函数,SQL SELECT max(avg(sal) 2 FROM emp 3 GROUP BY de