ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【FUNCTION】Oracle中的常见多行函数(组函数)

【FUNCTION】Oracle中的常见多行函数(组函数)

原创 Linux操作系统 作者:chicheng_cn421 时间:2011-04-28 14:11:07 0 删除 编辑

※ 下面内容整理时,参考了华夏名网的下面一篇文章。在此表示感谢!
 
Oracle中的常见单行函数 的介绍,可参考我的下面一篇学习笔记。
http://space.itpub.net/?uid-20335819-action-viewspace-itemid-693987
 
本篇整理内容包括
■组(多行)函数
■用GROUP BY给数据分组
■用HAVING子句限制分组数据
■嵌套函数

SQL中的组函数
组函数也叫集合函数,返回基于多个行的单一结果,
行的准确数量无法确定,除非查询被执行并且所有的结果都被包含在内。
和单行函数不同的是,在解析时所有的行都是已知的。
由于这种差别使组函数和单行函数有在需求和行为上有微小的差异.
 

■组(多行)函数
和单行函数相比,oracle提供了丰富的基于组的,多行的函数。
这些函数能在select或select的having子句中使用,当用于select子串时常常都和GROUP BY一起使用。
 
1、AVG([{DISYINCT|ALL}])
  返回数值的平均值。缺省设置为ALL.
SQL>
SQL> SELECT AVG(sal),AVG(ALL sal),AVG(DISTINCT sal) FROM scott.emp;
  AVG(SAL) AVG(ALLSAL) AVG(DISTINCTSAL)
---------- ----------- ----------------
2132.69230 2132.692307 2134.09090909091
SQL>
 
2、COUNT({*|DISTINCT|ALL} )
  返回查询中行的数目,缺省设置是ALL,*表示返回所有的行。
 
3、MAX([{DISTINCT|ALL}])
  返回选择列表项目的最大值,
  如果x是字符串数据类型,他返回一个VARCHAR2数据类型,
  如果X是个DATA数据类型,返回一个日期,
  如果X是numeric数据类型,返回一个数字。
  注意distinct和all不起作用,应为最大值和这两种设置是相同的。
 
4、MIN([{DISTINCT|ALL}])
  返回选择列表项目的最小值。
 
5、STDDEV([{DISTINCT|ALL}])
  返回选者的列表项目的标准差,所谓标准差是方差的平方根。
 
6、SUM([{DISTINCT|ALL}])
  返回选择列表项目的数值的总和。
 
7、VARIANCE([{DISTINCT|ALL}])
  返回选择列表项目的统计方差。
 

■用GROUP BY给数据分组

 组函数用来操作那些已分好组的数据,我们告诉数据库用GROUP BY怎样给数据分组或分类, 
当我们在SELECT语句的SELECT子句中使用组函数时,我们必须把为分组或非 常数 列放置在GROUP BY子句中,
 如果没有用group by进行专门处理,那么缺省的分类是将整个结果设为一类。
 
(1)我们用deptno字段分类
SQL>
SQL> select t.deptno, count(*) as cnt
  2  from emp t
  3  group by t.deptno
  4  /
DEPTNO        CNT
------ ----------
                1
    30          6
    20          5
    10          2
SQL>
 
(2)如果我们要将结果按照zip_codes排序,能用ORDER BY语句,ORDER BY子句能使用列或组函数。
SQL>
SQL> select t.deptno, count(*) as cnt
  2  from emp t
  3  group by t.deptno
  4  ORDER BY COUNT(*) DESC;
DEPTNO        CNT
------ ----------
    30          6
    20          5
    10          2
                1
SQL>
 

■用HAVING子句限制分组数据
当我们需要指示数据库对行分组,然后限制分组后的行的输出时,正确的方法是使用HAVING语句:
 
(1)不使用having子句进一步筛选的情况时:
SQL>
SQL> select t.deptno,sum(t.sal) as sum_sal
  2  from emp t
  3  group by t.deptno
  4  /
DEPTNO    SUM_SAL
------ ----------
    30       9400
    20      10875
    10       7450
SQL>
 
(2)使用having子句后
SQL>
SQL> select t.deptno,sum(t.sal) as sum_sal
  2  from emp t
  3  group by t.deptno
  4  having sum(t.sal) > 7451
  5  /
DEPTNO    SUM_SAL
------ ----------
    30       9400
    20      10875
SQL>
 

■嵌套函数

函数能够嵌套。一个函数的输出能是另一个函数的输入。各嵌套函数有有一个可继承的执行过程。
函数的优先权只是基于位置的,函数遵循由内到外,由左到右的原则。
嵌套技术一般用于象DECODE这样的能被用于逻辑判断语句IF.THENELSE的函数。
嵌套函数能包括在组函数中嵌入单行函数,或在单行函数或组函数中嵌入组函数。
SQL>
SQL> SELECT deptno,
  2         GREATEST(COUNT(DISTINCT job),COUNT(DISTINCT mgr)) cnt,
  3         COUNT(DISTINCT job) jobs,
  4         COUNT(DISTINCT mgr) mgrs
  5  FROM emp
  6  GROUP BY deptno;
DEPTNO        CNT       JOBS       MGRS
------ ---------- ---------- ----------
    10          2          2          1
    20          4          3          4
    30          4          4          2
                1          1          0
SQL>

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

请登录后发表评论 登录
全部评论

注册时间:2011-03-05

  • 博文量
    261
  • 访问量
    367512