Oracle专题6之Oracle的分组函数与数组分组

• 在关系数据库中，使用数据分组可以取得表数据的汇总信息。数据分组是通过分组函数、Group by以及having等子句共同实现的。

  1、分组函数

• SQL函数包含单行函数和多行函数。分组函数属于多行函数。作用于一组数据，并对一组数据返回一个值。
• 分组函数：SELECT [column,] group function(column) ... FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column];。
• 常用的分组函数：AVG、SUM、MIN、MAX、COUNT、WM_CONCAT.。

  a、AVG()

• 求平均值。举例：SELECT avg(sal) FROM emp;

  SQL> SELECT avg(sal) FROM emp;    AVG(SAL)            2135

  b、SUM()

• 求和。举例：SELECT SUM(sal) FROM emp;

  SQL> SELECT MIN(sal) FROM emp;    MIN(SAL)             800

  c、MIN

• 求最小值。举例：SELECT MIN(sal) FROM emp;

  SQL> SELECT MIN(sal) FROM emp;MIN(SAL)   800

d、COUNT()

• 计数（统计）。举例：求员工的总人数，SELECT COUNT(*) FROM emp;

  SQL> SELECT COUNT(*) FROM emp;COUNT(*)-    15

• 在使用分组时可以使用DISTINCT关键字来去重。

  SQL> select count(distinct deptno) from emp;COUNT(DISTINCTDEPTNO)                3

  e、WM_CONCAT行转列（让查询结果行转列）

• 该函数可以把列值以','号分隔起来，并显示成一行。

  SQL> select wm_concat(ename) from emp;WM_CONCAT(ENAME)EASON,G_EASON,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER

  2、Group by子句

• 作用：可以将表中的数据分成若干组。
• Group by子句的语法：SELECT [column,] group function(column), ... FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column];
• 例如：求出每个部分的平均工资，要求显示：部门号，部分的平均工资。
• SELECT deptno, avg(sal) from emp group by deptno;

  SQL> SELECT deptno, avg(sal) from emp group by deptno;DEPTNO   AVG(SAL)    30 1566.66667    20     2312.5    10 2916.66667

• 在SELECT列表中所有未包含在组函数中的列都应包含在GROUP BY子句中。如按照部门和职位进行分组：

  SQL> SELECT deptno, job, avg(sal) from emp group by deptno, job;        DEPTNO JOB         AVG(SAL)                20 CLERK            950                30 SALESMAN        1400                20 MANAGER         2975                30 CLERK            950                10 PRESIDENT       5000                30 MANAGER         2850                10 CLERK           1300                20 ANALYST         3000                10 MANAGER         2450已选择9行。

• 包含在group by 子句中的列不必包含在SELECT列表当中：

  SQL> select avg(sal) from emp group by deptno;    AVG(SAL)1566.66667        2312.52916.66667

• 再如示例：求出每个部分的员工姓名，要求显示：部分标号、员工姓名。

  SQL> select deptno, WM_concat(ename) from emp group by deptno;DEPTNO      WM_CONCAT(ENAME)    10            CLARK,MILLER,KING    20            EASON,FORD,ADAMS,SCOTT,JONES,G_EASON    30            ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD

  3、Having子句

• 作用：用于对分组后的结果进行过滤。
• 例子：请平均工资大于2500的部分，要求显示：部门号，平均工资]
• 不能再WHERE子句中使用组函数，可以在having子句中使用组函数。

  SQL> select deptno,avg(sal) from emp group by deptno having avg(sal) >2500;DEPTNO   AVG(SAL)    10 2916.66667

• having子句和group by子句通常一起使用。

• 如果条件中没有组函数，正常使用where语句就可以。比如说：select deptno, avg(sal) from emp where deptno=10 group by deptno;

  SQL> select deptno, avg(sal) from emp where deptno=10 group by deptno;DEPTNO   AVG(SAL)    10 2916.66667

• where使得分组记录数大大降低，从而提高效率。从SQL优化角度来看，尽量使用where子句。先过滤然后分组。

• 分组函数的嵌套：求部分平均工资的最大值。
• select max(avg(sal)) from emp group by deptno;

  SQL> select max(avg(sal)) from emp group by deptno;MAX(AVG(SAL))     2916.66667