oracle分组函数

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

分组函数的使用：

常用的分组函数：

Avg（求平均值）,sum（求总和）,min（求最小值）,max（求最大值）,count（求总个数）,wm_concat(行转列)

例：select count( distinct deptno) from emp ;

行转列： select deptno 部门号,wm_concat(ename) 部门中员工的名字  from emp group by deptno;

----------------------------------------------------

分组函数与空值：(分组函数会自动忽略空值)

1.      统计员工的平均工资

Select sum(sal)/count(*) 一，sum(sal)/count(sal) 二，avg(sal) 三 from emp;

2.      统计员工的平均奖金

Select sum(comm)/count(*)一,  sum(comm)/count(comm) 二，avg(comm) 三from emp;

注意：nvl函数使分组函数无法忽略空值

Selectsum(comm)/count(*)一,   sum(comm)/count(nvl(comm,0)) 二from emp;

Comm为空时，nvl函数返回0,0不为null,所以count不会忽略

------------------------------------------------------

Group by子句的使用

1.求部门号和部门的平均工资

Selectdeptno,avg(sal) from emp

Group bydeptno;

 抽象：

Selecta,b,c,avg(x) from table

Group bya,b,c;

规定：在select列表中所有未包含在组函数中的列都应该包含在group by 子句中；

相反的，在group by 中的列，不必包含在组函数中

多个列的分组：(分组逻辑：先按照group by 后面第一个列分组，如果第一个列相同，再按照第二个列分组，如果所有的列都相同，那么这就是同一组中的数据)

按照部门，不同的职位，统计员工工资的总额

Select deptno,job ,sum(sal) from emp

Group by deptno,job;

-------------------------------------------------------

Having子句的使用：（过滤分组后的数据）

平均工资大于2000的部门

Select deptno,avg(sal)

From emp

Having  avg(sql)>2000;

Where和having的区别：

在where子句后面不能使用分组函数

Where和having可以通用的情况(没有分组函数的情况下可以通用，但是从

Sql优化的角度上考虑，尽量使用where)：

查询10号部门的平均工资

1.      Where

Select deptno,avg(sal) from emp wheredeptno=10 group by deptno

2.      Having

Select deptno,avg(sal) from empgroup by deptno having deptno=10

-----------------------------------------------------------------------------

在分组函数中使用orderby 排序(默认是asc升序)

1.Select deptno ,avg(sal)

From emp

Group by deptno

Order by avg(sal)

2.

Select deptno ,avg(sal)

From emp

Group by deptno

Order by 2—这里的2代表select语句后面的第二列，这里即avg(sal)

----------------------------------------------------------------------------

分组函数的嵌套

Select  max(avg(sal))

From emp

Group by deptno;

-----------------------------------------------------------------------

Group by 语句的增强：

(1)Selext deptno,job,sum(sal)

From emp

Group by deptno,job

(2)Select deptno,sum(sal)

From emp

Having deptno

(3)Select sum(sal) from emp

 等价于（1）+（2）+（3）

Select deptno,job,sum(sal) from emp group byrollup(deptno,job);

语法：

Group by rollup(a,b)

等价于:groupby a,b+group by  a+group by null

Break on deptno skip 2;--相同的部门号只显示一次，不同的部门号之间

跳过两行

--详细的可以看看sql-plus的报表功能