oracle数据库sql语句聚集(分组)函数

1、 l完成本节课的学习后，应当完成下面的目标:识记常用的分组函数。描述分组函数的用处。使用GROUP BY 子句对元组进行分组使用HAVING 子句来筛选分组l分组函数对一组数据行进行操作，并对每个组得出一个结果. 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)- 5000AVG COUNT MAXMIN STDDEV SUMVARIANCESELECTcolumn, group_

2、function(column)FROMtableWHEREconditionGROUP BYcolumnORDER BYcolumn;AVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL)- - - - 1400 1600 1250 5600l对于数字类型的数据，可以使用 AVG和SUM 函数.SQL SELECTAVG(sal), MAX(sal), 2MIN(sal), SUM(sal) 3FROMemp 4WHEREjob LIKE SALES%; l可以对许多类型的数据使用MIN和MAX函数.SQL SELECTMIN(hiredate), MAX(hiredate)

3、 2 FROMemp;MIN(HIRED MAX(HIRED- -17-DEC-80 12-JAN-83 COUNT(*)- 6SQL SELECTCOUNT(*) 2 FROMemp 3 WHEREdeptno = 30;lCOUNT(*) 返回查询出的总行数.lCOUNT(expr) 返回expr值非空的行的数.SQL SELECTCOUNT(comm) 2 FROMemp 3 WHEREdeptno = 30;COUNT(COMM)- 4l分组函数忽略了行中所有的空行.SQL SELECT AVG(comm) 2 FROM emp; AVG(COMM)- 550lNVL函数使得分组函数可

4、以处理空值.SQL SELECT AVG(NVL(comm,0) 2 FROM emp;AVG(NVL(COMM,0)- 157.14286 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 DEPTNO AVG(SAL) - - 10 2916.6667 20 2175 30 1566.6667SELECTcolumn, group_function(column)FROMtableWHEREco

5、nditionGROUP BYgroup_by_expressionORDER BYcolumn;l将表中的数据行用GROUP语句分为几个组.l在SELECT语句中，没有使用分组函数的列必须在GROUP By子句中.SQL SELECT deptno, AVG(sal) 2 FROM emp 3 GROUP BY deptno; DEPTNO AVG(SAL)- - 10 2916.6667 20 2175 30 1566.6667lGROUP BY后面的列可以不出现在 SELECT链中.SQL SELECT AVG(sal) 2 FROM emp 3 GROUP BY deptno; AVG

6、(SAL)- 2916.6667 21751566.6667 DEPTNO JOB SAL- - - 10 MANAGER 2450 10 PRESIDENT 5000 10 CLERK 1300 20 CLERK 800 20 CLERK 1100 20 ANALYST 3000 20 ANALYST 3000 20 MANAGER 2975 30 SALESMAN 1600 30 MANAGER 2850 30 SALESMAN 1250 30 CLERK 950 30 SALESMAN 1500 30 SALESMAN 1250JOB SUM(SAL)- -CLERK 1300MANAG

7、ER 2450PRESIDENT 5000ANALYST 6000CLERK 1900MANAGER 2975CLERK 950MANAGER 2850SALESMAN 5600DEPTNO-101010202020303030SQL 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

8、.l在SELECT 语句中，任何不在聚组函数中出现的列，必须在 GROUP BY 子句中.SQL SELECTdeptno, COUNT(ename) 2 FROMemp;SELECT deptno, COUNT(ename) *ERROR at line 1:ORA-00937: not a single-group group function不能在 WHERE子句中对列做出限定.使用 HAVING 子句来限定分组.SQL SELECT deptno, AVG(sal) 2 FROM emp 3 WHERE AVG(sal) 2000 4 GROUP BY deptno;WHERE AVG

9、(sal) 2000 *ERROR at line 3:ORA-00934: group function is not allowed here 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 DEPTNO MAX(SAL)- - 10 5000 20 3000l用 HAVING 子句来限定分组行被分组了.使用了分组函数.符合 HAVING子句条件的组将被显示出来.SELECTcolumn, g

10、roup_functionFROMtableWHEREconditionGROUP BYgroup_by_expressionHAVINGgroup_conditionORDER BYcolumn;SQL SELECT deptno, max(sal) 2 FROM emp 3 GROUP BY deptno 4 HAVING max(sal)2900; DEPTNO MAX(SAL)- - 10 5000 20 3000SQL SELECT job, SUM(sal) PAYROLL 2 FROM emp 3 WHERE job NOT LIKE SALES% 4 GROUP BY job 6 ORDER BY SUM(sal);JOB PAYROLL- -ANALYST 6000MANAGER 82755 HAVING SUM(sal)5000SQL SELECT max(avg(sal) 2 FROM emp 3 GROUP BY deptno;MAX(AVG(SAL)- 2916.6667l显示最大的平均工资. S