OCP 复习笔记之PL/SQL (5)

最近打算把9i 的OCP教程看一遍，作一些简单的笔记，作为备忘。

PL/SQL看似简单，但实际使用起来还是有很多技巧和误区的。以下记录的是复习过程中想到的、和容易出现错误的地方。

这些东西在教程大部分是没有记载的。

第五章 聚合函数

5.1 AVG（求平均值）

Ø 格式：AVG([DISTINCT|ALL]n)

默认是ALL，如果需要返回不重复的列表需要指明DISTINCT。如果加上DISTINCT关键字，则先过滤重复的n及null值，然后再求平均值。

Ø AVG函数内的参数只能是数字型的

Ø AVG函数会忽略空值；如果需要把当前值为空值的记录也计算进去，则用NVL之类的函数来辅助。如：

SQL> select * from t1;

ID NAME

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

2 2

2

SQL> select avg(id) from t1;

AVG(ID)

----------

2

SQL> select avg(nvl(id,0)) from t1;

AVG(NVL(ID,0))

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

1

5.2 COUNT（求记录个数）

Ø 格式：COUNT({*|[DISTINCT|ALL]expr})

默认的是ALL，如果需要过滤重复值则加上DISTINCT

Ø 如果参数是“*”或者常数、常量,则返回的是符合条件的记录总数，包括记录中有空值的记录；如果参数是列名，则返回的是符合条件、且该列对应的值不为空的记录数。

Ø 可用于任意数据类型，忽略空值

5.3 MAX（求表达式最大值）

Ø 格式：MAX([DISTINCT|ALL]expr)

Ø 可以用于任意数据类型；忽略空值

5.4 MIN

Ø 格式：MIN([DISTINCT|ALL]expr)

Ø 可用于任意数据类型，忽略空值

5.5 STDDEV

Ø 格式：STDDEV([DISTINCT|ALL]x)

Ø 只能用于数值类型，忽略空值

5.6 SUM

Ø 格式：SUM([DISTINCT|ALL]n)

Ø 只能用于数值类型的求和，忽略空值

5.7 VARIANCE

Ø 格式：VARIANCE([DISTINCT|ALL]x)

Ø 只能用于数值类型，忽略空值

5.8 GROUP BY

Ø Select子句中的列必须出现在group by子句中，但select子句中的包含列名的表达式在group by中可以只包含该列名，也可以是与select子句中一样的表达式；出现在group by子句中的列或表达式不一定需要出现在select子句中。

Ø select子句中中的常量不需要出现在group by子句中

Ø Group by子句不能用列的别名

Ø 默认情况下，返回结果是以group by子句中的列升序排列的。（10g是无序的）

Ø 聚合函数可以嵌套，最外层的聚合函数是以里层的聚合函数得到的结果作为数据源。也就是说即使是有group by的sql，返回的记录数也是一行。

SQL> select max(avg(id)) from t1 group by id;

MAX(AVG(ID))

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

5

5.9 HAVING子句

Ø 如果根据聚合函数进行数据过滤的话，只能用having子句，不能把聚合函数写在where子句中；having子句可以带非聚合函数的限制条件，但用来限定范围的列必须包含在group by子句中（不一定需要出现在select子句中）。

Ø 在sql中，执行顺序是：where -> group by -> having

Ø 一般情况下，可以写在where子句中的限定条件就写在where子句中，写在having子句中性能会较差。原因是条件写在where子句中，先得到过滤后的记录再group by，参与group by的记录就少，写在having子句中，是先group by再过滤（和分页优化原理类似）

Ø Having子句中不能用聚合函数的字段别名

Ø Having子句可以写在group by子句前，但为了看起来更舒服，不提倡写在group by前。

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

转载于:http://blog.itpub.net/231499/viewspace-63772/