数据库Oracle之分组函数



1、分组函数

         Count、sum、avg、MAX、MIN

 

 

--求员工的平均工资

 select avg(sal) AA, sum(sal)/count(empno) BBfrom emp;

 

 --求员工的平均comm 奖金

 select avg(comm) AA, sum(comm)/count(empno)BB, sum(comm)/count(comm) CC from emp;

 

 造成这个问题的原因:奖金含有空值, 组函数能自动滤空

                  

                   SQL>select count(empno) ,count(comm), count(*) from emp;

                  

                   COUNT(EMPNO)COUNT(COMM)   COUNT(*)

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

                             14           4         14

 

--count(comm)  count()组函数, 组函数自动滤空

 select count(empno) ,count(nvl(comm, 0)),count(*) from emp;

 

 

 

2 、分组数据(对数据进行分组....)

 

 1 --查询部门的平均工资

 select avg(sal)

 fromemp

 group by deptno --对部门进行分组 10部门1组 20部门1组 30部门1组

 

 //抽象........

 select a, b, c

 fromemp

 group by a, b, c, d //ok

 

 select a, b, c d, e

 fromemp

 group by a, b, c, d //err

 

 select deptno

 fromemp

 group by deptno --对部门进行分组 10部门1组 20部门1组 30部门1组

 

 --在使用分组函数时,在select中出现的a b c列, 必须是group by 后面集合的子集

 --在group by 中没有出现的项,不能再select 进行检索

 -- 先对数据进行分组，然后再组上进行数据查询

 

 2 --group by后面有多列

 -- 查询部门的平均工资

 -- 查部门\ 不同的职位 统计平均工资

 

                     1  select deptno, job, avg(sal)

                     2  from emp

                     3  group by deptno, job

                   4* order by 1

                   SQL>/

                  

                       DEPTNO JOB         AVG(SAL)

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

                           10 CLERK           1300

                           10 MANAGER         2450

                           10 PRESIDENT       5000

                           20 ANALYST         3000

                           20 CLERK            950

                           20 MANAGER         2975

                           30 CLERK            950

                           30 MANAGER         2850

                           30 SALESMAN        1400

                  

                   已选择9行。

3 、有关过滤分组

(对数据分组以后,在增加检索条件)：having

 

1 --查询部门的平均工资

2 -- 进一步,查询平均工资大于2000的部门

 

          select deptno

          from emp

          

          group by deptno

          having avg(sal) > 2000

                  

                       DEPTNO  AVG(SAL)

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

                           20      2175

                           10 2916.66667

                          

4 、having 和 where子句  

 

求10号部门的平均工资

1 先分组,再在having过滤

select avg(sal)

from emp

group by deptno

having deptno=10

 

2 先where过滤 ,在分组

 

select avg(sal)

from emp

where deptno = 10

group by deptno

 

--sql优化2 先where过滤 ,在分组

 

 

有关where子句和having子句都可以条件过滤

最大区别: 在where子句中不能有组函数

 

        

           1 select avg(sal)

           2  fromemp

           3* where deptno = avg(sal);

         SQL>/

         wheredeptno = avg(sal);

                        *

         第 3 行出现错误:

         ORA-00934:此处不允许使用分组函数