ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle中组函数

oracle中组函数

原创 Linux操作系统 作者:wengjie_0627 时间:2011-09-23 09:38:51 0 删除 编辑

组函数的使用(组函数最多只能嵌套两次,并且只有在group by之后再能嵌套两次,如果没有group by,则只能嵌套一次)

1.特点:每组返回一个结果集。

avg:求平均值,会忽略null值。
count:求总个数,只有count(*)会包括null值,其他都忽略null值
max:求最大值,会忽略null值。
min:求最小值,会忽略null值。
sum:求总数,会忽略null值。

 

max,min :求最大和最小值。
max和min里面可以是数字类型,字符类型和日期类型

SQL> select max(sal),min(sal),max(ename),min(ename) from emp;

  MAX(SAL)   MIN(SAL) MAX(ENAME) MIN(ENAME)
---------- ---------- ---------- ----------
      5000        800 WARD       ADAMS

 这里的字符类型比较的是它的ASCII值。


AVG,sum,variance,stddev 这些函数只能是数字类型的。

 

count函数的使用:
返回的是一个列中的行数。


count函数的三种形式:

count(*):返回行数,包含相同值,null值。

SQL> select count(*) total_rows from emp;

TOTAL_ROWS
----------
        14


count(expr):返回不含null值的总和,会忽略null值。
SQL> select count(comm) from emp;

COUNT(COMM)
-----------
          4


count(distinct expr):返回不含null值和不相同的值的总和,会忽略null值。

SQL> select count(distinct sal) from emp;

COUNT(DISTINCTSAL)
------------------
                12


在这几个函数中除了count(*)不会会忽略null值。
其他的函数都会忽略null,因为如果不忽略那么在计算的时候
时候,就会产生null值。null与任何数计算都为null。


但是在计算的时候,有时候需要那些没有值的列。
比如:计算所有员工的平均奖金。


SQL> select avg(comm) from emp;

 AVG(COMM)
----------
       550

SQL> select count(comm) from emp;

COUNT(COMM)
-----------
          4

SQL> select sum(comm) from emp;

 SUM(COMM)
----------
      2200

 这里只是计算四个人的。题目需要计算所有人员工。
 这里我们需要有NVL函数来处理null值。

SQL> select avg(nvl(comm,0)) from emp;

AVG(NVL(COMM,0))
----------------
      157.142857


group by:
如果select子句中的字段没有在聚合函数中,那么就必须出现在
group by 子句中。而出现在group by子句中不一定要出现在select子句中。

SQL> select deptno , avg(sal) from emp;
select deptno , avg(sal) from emp
       *
ERROR at line 1:
ORA-00937: not a single-group group function

正确写法:
SQL> select deptno , avg(sal) from emp
  2  group by deptno;

    DEPTNO   AVG(SAL)
---------- ----------
        30 1566.66667
        20       2175
        10 2916.66667


注意:组函数不能够用在where子句后面。(因为sql语句的执行顺序是先执行from子句,在执行where子句,在执行group by子句,在执行聚合函数,在执行having子句,在执行select,最后执行order by,如果在where子句中使用组函数,由于还没有执行group by 子句,所以不知道如何分组,不能计算avg(sal)的值,导致报错)
SQL> select deptno , avg(sal) from emp
  2  where avg(sal)>2000;
where avg(sal)>2000
      *
ERROR at line 2:
ORA-00934: group function is not allowed here

我们可以使用having来过滤分组过后的数据。
SQL> select deptno , avg(sal) from emp
  2  group by deptno
  3  having avg(sal)>2000;

    DEPTNO   AVG(SAL)
---------- ----------
        20       2175
        10 2916.66667

having是过滤分组过后的数据,在having组函数不能用列别名。(由于sql语句的执行顺序,select语句的执行顺序比having要晚,所以不能够识别列别名,导致报错)(根据sql语句的执行顺序可知,只有order by的执行顺序比select晚,所以只有order by才可以使用列别名)
select deptno , avg(sal)avg_sal from emp
group by deptno
having avg_sal>2000
SQL> /
having avg_sal>2000
       *
ERROR at line 3:
ORA-00904: "AVG_SAL": invalid identifier


having子句也可以用在group By 的前面,但是建议用在group by 的后面,以便于
逻辑理解。

having 和 where的区别:
1.having只能用在group by 后面。有having肯定有group by子句。
2.where子句用在having前面。
3.having 是用于分组统计过后的过滤,而where是过滤的是原始数据,也就是分组统计之前的数据。

having后面最好只限定组函数,如:
求出employees表中部门号小于90的最大工资大于10000的工资
1.select department_id ,max(salary) from employees where department_id<90 group by department_id having max(salary)>10000;
2.select department_id ,max(salary) from employees  group by department_id having max(salary)>10000 and department_id<90;
1和2语句虽然最后得出的结果都正确,但是2语句比1语句中执行了更多的max()(因为2语句对department_id<90的也惊醒max操作),会对执行效率产生影响

当使用GROUP BY子句时Orale服务器会自动对结果集合默认按GROUP BY子句所指定的列升序排列

按多个列分组
  例:
     SELECT   deptno,job,sum(sal)
     FROM     emp
     GROUP BY deptno,job;
oracle会先对deptno分组,当deptno相同时,在对job分组

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24984814/viewspace-708162/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2011-05-19

  • 博文量
    50
  • 访问量
    27802