【FUNCTION】Oracle中的常见多行函数（组函数）

※ 下面内容整理时，参考了华夏名网的下面一篇文章。在此表示感谢！

http://www.sudu.cn/info/article/articleInfo.php?aId=320982

 

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20335819/viewspace-693995/