oracle--分组函数

分组函数

分组函数作用于一组数据，并对一组数据返回一个值。

常用的组函数

avg count max min sum

例：

SQL> --工资总额 SQL> select sum(sal) from emp; SUM(SAL) ---------- 29025 SQL> --人数 SQL> select count(*) from emp; COUNT(*) ---------- 14 SQL> --平均工资 SQL> select sum(sal)/count(*) 一,avg(sal) 二 from emp; 一 二 ---------- ---------- 2073.21429 2073.21429 SQL> --平均奖金 SQL> select sum(comm)/count(*) 一,sum(comm)/count(comm) 二,avg(comm) 三 2 from emp; 一 二 三 ---------- ---------- ---------- 157.142857 550 550 SQL> select count(*),count(comm) from emp; COUNT(*) COUNT(COMM) ---------- ----------- 14 4

null值：

SQL> --null值 5.组函数（多行函数）自动滤空 SQL> select count(*),count(nvl(comm,0)) from emp; COUNT(*) COUNT(NVL(COMM,0)) ---------- ------------------ 14 14 SQL> --null值 5.组函数（多行函数）自动滤空;可以嵌套滤空函数 来屏蔽他的滤空功能

distinct关键字：

count(distinct expr) 返回expr非空不重复的记录总数 语法：select count(distinct deptno) from emp;

分组数据

语法：group by 子句语法 可以使用group by子句将表中的数据分成若干组

注意：

在select列表中所有未包含在组函数中的列都应该包含在group by子句中 包含在group by子句中的列不必包含在select列表中

例：

SQL> --求每个部门的平均工资 SQL> select deptno,avg(sal) 2 from emp 3 group by deptno; SQL> --多个列的分组 SQL> select deptno,job,sum(sal) 2 from emp 3 group by deptno,job 4 order by 1;

使用多个列分组：

SQL> --多个列的分组 SQL> select deptno,job,sum(sal) 2 from emp 3 group by deptno,job 4 order by 1;

过滤分组：

SQL> --求平均工资大于2000的部门 SQL> select deptno,avg(sal) 2 from emp 3 group by deptno 4 having avg(sal) > 2000; DEPTNO AVG(SAL) ---------- ---------- 20 2175 10 2916.66667

注意：

不能再where子句中使用组函数 可以在having子句中使用组函数 SQL> --where和having的区别：where后面不能使用多行函数 SQL> --查询10号部门的平均工资 SQL> select deptno,avg(sal) 2 from emp 3 group by deptno 4 having deptno=10; select deptno,avg(sal) 2 from emp 3 where deptno=10 4 group by deptno

SQL 优化原则：

3. 尽量使用where having：先分组，再过滤 where：先过滤，再分组 所以where效率更高

group by的增强

SQL> /* SQL> group by的增强 SQL> select deptno,job,sum(sal) from emp group by deptno,job SQL> + SQL> select deptno,sum(sal) from emp group by deptno SQL> + SQL> select sum(sal) from emp SQL> SQL> === SQL> SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job); SQL> SQL> 抽象 SQL> group by rollup(a,b) SQL> == SQL> group by a,b SQL> + SQL> group by a SQL> + SQL> group by null SQL> SQL> */ SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);