oracle function 返回多值,【FUNCTION】Oracle中的常见多行函数（组函数）

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

本篇整理内容包括

■组(多行)函数

■用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>