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前。