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.5 2916.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
当前标题:Oracle专题6之Oracle的分组函数与数组分组
文章出自:http://scyanting.com/article/ggjiio.html