Oracle 数据库 简单Select语句之主讲教师：group by和 having分句本章目标 Group by 子句 Having 子句 嵌套分组函数SQL语句：GROUP BYEMPEMP“maximum “maximum salary in salary in the EMP table”the EMP table”DEPTNO SAL - -10 245010 500010 130020 80020 110020 300020 300020 297530 160030 285030 125030 95030 150030 1250MAX(SAL) -5000SQL语句：GROUP BY （续） 根据字段值对行进行分组 该子句在应用时将与聚合函数联合 也可以在已分组的查询中完成条件检索Select AuthorID,count(BookId) From Book_TB Group By AuthorIDSQL语句：GROUP BY 函 数 AVG COUNT MAX MIN STDDEV SUM VARIANCESQL语句：GROUP BY 语 法SELECTcolumn, group_function(column) FROMtable WHEREcondition GROUP BYcolumn ORDER BYcolumn;SQL语句：GROUP BY AVG、 SUM、MAX、MINAVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL) - - - -1400 1600 1250 5600SQL SELECTAVG(sal), MAX(sal),2MIN(sal), SUM(sal)3FROMemp4WHEREjob LIKE SALES%;SQL语句:GROUP BY MAX、MINSQL SELECTMIN(hiredate), MAX(hiredate)2 FROMemp;MIN(HIRED MAX(HIRED - - 17-DEC-80 12-JAN-83SQL语句：GROUP BY COUNTCOUNT(*) -6SQL SELECTCOUNT(*)2 FROMemp3 WHEREdeptno = 30;SQL语句:GROUP BY COUNT （续）SQL SELECTCOUNT(comm)2 FROMemp3 WHEREdeptno = 30;COUNT(COMM) -4SQL语句：GROUP BY 分组 函数中的NULL值SQL SELECT AVG(comm)2 FROM emp;AVG(COMM) -550SQL语句：GROUP BY 分组 函数中的NULL值（续）SQL SELECT AVG(NVL(comm,0)2 FROM emp;AVG(NVL(COMM,0) -157.14286SQL语句：GROUP BY 数据 分组EMPEMP2916.6667 2916.66672175 21751566.6667 1566.6667DEPTNO SAL - -10 245010 500010 130020 80020 110020 300020 300020 297530 160030 285030 125030 95030 150030 1250DEPTNO AVG(SAL)- -10 2916.666720 217530 1566.6667SQL语句：GROUP BY 数据 分组（续）SQL SELECT deptno, AVG(sal)2 FROM emp3 GROUP BY deptno;DEPTNO AVG(SAL) - -10 2916.666720 217530 1566.6667SQL语句：GROUP BY 数据 分组（续） Group by 子句中的列不一定出现在 Select List中SQL SELECT AVG(sal)2 FROM emp3 GROUP BY deptno;AVG(SAL) - 2916.66672175 1566.6667SQL语句：GROUP BY 多列 分组 EMPEMPDEPTNO JOB SAL - - -10 MANAGER 245010 PRESIDENT 500010 CLERK 130020 CLERK 80020 CLERK 110020 ANALYST 300020 ANALYST 300020 MANAGER 297530 SALESMAN 160030 MANAGER 285030 SALESMAN 125030 CLERK 95030 SALESMAN 150030 SALESMAN 1250JOB SUM(SAL) - - CLERK 1300 MANAGER 2450 PRESIDENT 5000 ANALYST 6000 CLERK 1900 MANAGER 2975CLERK 950MANAGER 2850 SALESMAN 5600DEPTNO - 10 10 10 20 20 203030 30SQL语句：GROUP BY 多列 分组（续）SQL SELECT deptno, job, sum(sal)2 FROM emp3 GROUP BY deptno, job;DEPTNO JOB SUM(SAL) - - -10 CLERK 130010 MANAGER 245010 PRESIDENT 500020 ANALYST 600020 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 functionColumn missing in the GROUP BY clauseColumn missing in the GROUP BY clause本章目标 Group by 子句 Having 子句 嵌套分组函数SQL语句： HAVINGSQL SELECT deptno, AVG(sal)2 FROM emp3 WHERE AVG(sal) 20004 GROUP BY deptno;WHERE AVG(sal) 2000* ERROR at line 3: ORA-00934: group function is not allowed hereCannot use the WHERE clauseCannot use the WHERE clauseto restrict groups to restrict groupsSQL语句： HAVING （续） 与“Where”子句相似，只不过 “Where”用于行，而“Having”用于已分组的结果 示例 SELECT category_code, MAX(cost) FROM booksGROUP BY category_code HAVING MAX(cost) 1000结果 category_code MAX(cost) 科幻 1299编写的查询按下列方式执行 找出每一种类的图书的最高价 筛出最高价 1000 的 图书种类SQL语句： HAVING （续）“maximum“maximum salarysalary per departmentper department greater thangreater than $2900”$2900”EMPEMP500050003000300028502850DEPTNO SAL - -10 245010 500010 130020 80020 110020 300020 300020 297530 160030 285030 125030 95030 150030 1250DEPTNO MAX(SAL) - -10 500020 3000SQL语句： HAVING （续）SQL SELECT deptno, max(sal)2 FROM emp3 GROUP BY deptno4 HAVING max(sal)2900;DEPTNO MAX(SAL) - -10 500020 3000SQL语句： HAVING （续）SQL SELECT job, SUM(sal) PAYROLL2 FROM emp3 WHERE job NOT LIKE SALES%4 GROUP BY job6 ORDER BY SUM(sal);JOB PAYROLL - - ANALYST 6000 MANAGER 82755 HAVING SUM(sal)5000本章目标 Group by 子句 Having 子句 嵌套分组函数嵌套分组函数SQL SELECT max(avg(sal)2 FROM emp3 GROUP BY deptno;MAX(AVG(SAL) -2916.6667本章总结 Group by 子句 Having 子句 嵌套分组函数SELECTcolumn, group_function(column) FROMtable WHEREcondition GROUP BYgroup_by_expression HAVINGgroup_condit