《简单Select语句之groupby和having分句.ppt》由会员分享，可在线阅读，更多相关《简单Select语句之groupby和having分句.ppt（27页珍藏版）》请在三一办公上搜索。
1、Oracle 数据库 简单Select语句之,主讲教师：,4,group by和 having分句,本章目标,Group by 子句Having 子句 嵌套分组函数,SQL语句：GROUP BY,EMP,“maximum salary in the EMP table”,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,SQL语句：GROUP BY（续）,根据字段值对行进行分组
2、 该子句在应用时将与聚合函数联合 也可以在已分组的查询中完成条件检索Select AuthorID,count(BookId)From Book_TB Group By AuthorID,SQL语句：GROUP BY 函数,AVG COUNT MAXMIN STDDEV SUMVARIANCE,SQL语句：GROUP BY 语法,SELECTcolumn,group_function(column)FROMtableWHEREconditionGROUP BYcolumnORDER BYcolumn;,SQL语句：GROUP BY AVG、SUM、MAX、MIN,AVG(SAL)MAX(SAL
3、)MIN(SAL)SUM(SAL)-1400 1600 1250 5600,SQL SELECTAVG(sal),MAX(sal),2MIN(sal),SUM(sal)3FROMemp 4WHEREjob LIKE SALES%;,SQL语句:GROUP BY MAX、MIN,SQL SELECTMIN(hiredate),MAX(hiredate)2 FROMemp;,MIN(HIRED MAX(HIRED-17-DEC-80 12-JAN-83,SQL语句：GROUP BY COUNT,COUNT(*)-6,SQL SELECTCOUNT(*)2 FROMemp 3 WHEREdeptno
4、=30;,SQL语句:GROUP BY COUNT（续）,SQL SELECTCOUNT(comm)2 FROMemp 3 WHEREdeptno=30;,COUNT(COMM)-4,SQL语句：GROUP BY 分组 函数中的NULL值,SQL SELECT AVG(comm)2 FROM emp;,AVG(COMM)-550,SQL语句：GROUP BY 分组函数中的NULL值（续）,SQL SELECT AVG(NVL(comm,0)2 FROM emp;,AVG(NVL(COMM,0)-157.14286,SQL语句：GROUP BY 数据分组,EMP,2916.6667,2175,1
5、566.6667,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.6667,SQL语句：GROUP BY 数据分组（续）,SQL SELECT deptno,AVG(sal)2 FROM emp 3 GROUP BY deptno;,DEPTNO AVG(SAL)-10 2916.6667 20 2175 30 1
6、566.6667,SQL语句：GROUP BY 数据分组（续）,Group by 子句中的列不一定出现在 Select List中,SQL SELECT AVG(sal)2 FROM emp 3 GROUP BY deptno;,AVG(SAL)-2916.6667 21751566.6667,SQL语句：GROUP BY 多列分组,EMP,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 MAN
7、AGER 2975 30 SALESMAN 1600 30 MANAGER 2850 30 SALESMAN 1250 30 CLERK 950 30 SALESMAN 1500 30 SALESMAN 1250,JOB SUM(SAL)-CLERK 1300MANAGER 2450PRESIDENT 5000ANALYST 6000CLERK 1900MANAGER 2975CLERK 950MANAGER 2850SALESMAN 5600,DEPTNO-101010202020303030,SQL语句：GROUP BY 多列分组（续）,SQL SELECT deptno,job,sum(
8、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.,SQL语句：GROUP BY 限制,SQL SELECTdeptno,COUNT(ename)2 FROMemp;,SELECT deptno,COUNT(ename)*ERROR at line 1:ORA-00937:not a single-group group function,Co
9、lumn missing in the GROUP BY clause,本章目标,Group by 子句Having 子句 嵌套分组函数,SQL语句：HAVING,SQL SELECT deptno,AVG(sal)2 FROM emp 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,Cannot use the WHERE clause to restrict groups,SQL语句：HAVING（续
10、）,与“Where”子句相似，只不过“Where”用于行，而“Having”用于已分组的结果,示例 SELECT category_code,MAX(cost)FROM booksGROUP BY category_codeHAVING MAX(cost)1000结果category_code MAX(cost)科幻 1299,编写的查询按下列方式执行 找出每一种类的图书的最高价 筛出最高价 1000 的 图书种类,SQL语句：HAVING（续）,“maximumsalaryper departmentgreater than$2900”,EMP,DEPTNO SAL-10 2450 10 5
11、000 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 3000,SQL语句：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,SQL语句：HAVING（续）,SQL 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 8275,5 HAVING SUM(sal)5000,本章目标,Group by 子句Having 子句 嵌套分组函数,嵌套分组函数,SQL SELECT max(avg(sal)2 FROM emp 3 GROUP BY deptno;,MAX(AVG(SAL)-2916.6667,本章总结,Group by 子句Having 子句 嵌套分组函数,
