简单Select语句之groupby和having分句.ppt

《简单Select语句之groupby和having分句.ppt》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、Oracle数据库简单Select语句之主讲教师：groupby和having分句本章目标Groupby子句Having子句嵌套分组函数SQL语句：GROUPBYEMP“maximumsalaryintheEMPtable”DEPTNOSAL------------------1024501050001013002080020110020300020300020297530160030285030125030950301500301250MAX(SAL)---------5000SQL语句：GROUPBY（续

2、）根据字段值对行进行分组该子句在应用时将与聚合函数联合也可以在已分组的查询中完成条件检索SelectAuthorID,count(BookId)FromBook_TBGroupByAuthorIDSQL语句：GROUPBY－函数AVGCOUNTMAXMINSTDDEVSUMVARIANCESQL语句：GROUPBY－语法SELECT[column,]group_function(column)FROMtable[WHEREcondition][GROUPBYcolumn][ORDERBYcolumn];SQL语

3、句：GROUPBY－AVG、SUM、MAX、MINAVG(SAL)MAX(SAL)MIN(SAL)SUM(SAL)-----------------------------------1400160012505600SQL>SELECTAVG(sal),MAX(sal),2MIN(sal),SUM(sal)3FROMemp4WHEREjobLIKE'SALES%';SQL语句:GROUPBY－MAX、MINSQL>SELECTMIN(hiredate),MAX(hiredate)2FROMemp;MIN(HIR

4、EDMAX(HIRED------------------17-DEC-8012-JAN-83SQL语句：GROUPBY－COUNTCOUNT(*)---------6SQL>SELECTCOUNT(*)2FROMemp3WHEREdeptno=30;SQL语句:GROUPBY－COUNT（续）SQL>SELECTCOUNT(comm)2FROMemp3WHEREdeptno=30;COUNT(COMM)-----------4SQL语句：GROUPBY－分组函数中的NULL值SQL>SELECTAVG(com

5、m)2FROMemp;AVG(COMM)---------550SQL语句：GROUPBY－分组函数中的NULL值（续）SQL>SELECTAVG(NVL(comm,0))2FROMemp;AVG(NVL(COMM,0))----------------157.14286SQL语句：GROUPBY－数据分组EMP2916.666721751566.6667DEPTNOSAL------------------1024501050001013002080020110020300020300020297530160

6、030285030125030950301500301250DEPTNOAVG(SAL)----------------102916.6667202175301566.6667SQL语句：GROUPBY－数据分组（续）SQL>SELECTdeptno,AVG(sal)2FROMemp3GROUPBYdeptno;DEPTNOAVG(SAL)------------------102916.6667202175301566.6667SQL语句：GROUPBY－数据分组（续）Groupby子句中的列不一定出现在Se

7、lectList中SQL>SELECTAVG(sal)2FROMemp3GROUPBYdeptno;AVG(SAL)---------2916.666721751566.6667SQL语句：GROUPBY－多列分组EMPDEPTNOJOBSAL---------------------------10MANAGER245010PRESIDENT500010CLERK130020CLERK80020CLERK110020ANALYST300020ANALYST300020MANAGER297530SALESMAN

8、160030MANAGER285030SALESMAN125030CLERK95030SALESMAN150030SALESMAN1250JOBSUM(SAL)------------------CLERK1300MANAGER2450PRESIDENT5000ANALYST6000CLERK1900MANAGER2975CLERK950MANAGER2850SALESMAN5600D