先來一段概念:

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

 DECODE函式是ORACLE PL/SQL是功能強大的函式之一，目前還只有ORACLE公司的SQL提供了此函式，其他資料庫廠商的SQL實現還沒有此功能。DECODE有什麼用途 呢？ 先構造一個例子，假設我們想給智星職員加工資，其標準是：工資在8000元以下的將加20％；工資在8000元以上的加15％，通常的做法是，先選出記錄 中的工資欄位值? select salary into var-salary from employee，然後對變數var-salary用if-then-else或choose case之類的流控制語句進行判斷。 如果用DECODE函式，那麼我們就可以把這些流控制語句省略，透過SQL語句就可以直接完成。如下：select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2,salary from employee 是不是很簡潔？ DECODE的語法：DECODE(value,if1,then1,if2,then2,if3,then3,...,else)，表示如果value 等於if1時，DECODE函式的結果返回then1,...,如果不等於任何一個if值，則返回else。初看一下，DECODE 只能做等於測試，但剛才也看到了，我們透過一些函式或計算替代value，是可以使DECODE函式具備大於、小於或等於功能。

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

在 開發統計報表的過程中，經常會碰到在查詢到的資料集中，插入一些小計行和合計行。比如在烤煙系統中，幾乎每個統計報表都需要加入“合計”行，還有不少涉及 到菸葉等級的統計報表需要加入各菸葉等級的小計行。我看到不少人（包括我自己）都是在程式中專門寫一些方法來處理的，有的方法還很繁瑣。最近在看SQL Server2000的聯機叢書中才發現，利用GROUPING聚合函式和ROLLUP運算子可以輕鬆實現統計中加入小計和合計功能。

1.      GROUPPING和ROLLUP的基本知識

1.1.    GROUPPING

是一個聚合函式,它產生一個附加的列，當用 CUBE 或 ROLLUP 運算子新增行時，附加的列輸出值為1，當所新增的行不是由 CUBE 或 ROLLUP 產生時，附加列值為0。

語法: GROUPING ( column_name )引數: column_name是 GROUP BY 子句中用於檢查 CUBE 或 ROLLUP 空值的列。返回型別: int備註: 分組用於區分由 CUBE 和 ROLLUP 返回的空值和標準的空值。作為CUBE 或 ROLLUP 操作結果返回的 NULL 是 NULL 的特殊應用。

1.2.    ROLLUP

ROLLUP 運算子生成聚合彙總, 需要彙總資訊時，此運算很有用。該運算子生成的結果集類似於 CUBE 運算子生成的結果集。但它們兩者有一些區別，CUBE 生成的結果集顯示了所選列中值的所有組合的聚合。而ROLLUP 生成的結果集顯示了所選列中值的某一層次結構的聚合。

語法: ROLLUP ( column_name1[，column_name2。。。] )用法: 用在GROUP BY 子句中。對那些需要按其分組，並對其分組的聚合資料進行彙總的列，就請對這些列加上ROLLUP運算子。注意: “GROUP BY ROLLUP(col1,col2)”與“GROUP BY ROLLUP(col1),ROLLUP(col2)”是有區別的。“GROUP BY ROLLUP(col1),ROLLUP(col2)”其實就相當於“GROUP BY CUBE(col1,col2)”，因為它對每個分組的聚合都要進行彙總。“GROUP BY ROLLUP(col1,col2)”與“GROUP BY ROLLUP(col2,col1)”也有區別，前者是對每個col1的唯一值都產生一個在col1下各個col2聚合值彙總的行，再對所有col1與col2的聚合值產生一個彙總行；而後者是對每個col2的唯一值都產生一個在col2下各個col1聚合值的彙總行，再對所有col1與col2的聚合值產生一個彙總行。這樣說邏輯可能不太清晰，我們看一下下面的圖表就一目瞭然了。

col1	col2	amount		col1	col2	amount
3	2	3584777		3	2	3584777
3	3	12774875		4	2	200789.1
3		16359652		5	2	274432
4	2	200789.1			2	4059998
4	3	8619498		3	3	12774875
4		8820288		4	3	8619498
5	2	274432		5	3	2024463
5	3	2024463			3	23418837
5		2298895				27478835
		27478835
GROUP BY ROLLUP(col1,col2)的效果				GROUP BY ROLLUP(col2,col1)的效果

 

2.      實際案例

我們現在就來看一個Oracle9i中的統計示例，示例如下：

2.1.    統計要求

開發一個關於各菸葉等級的二次驗級的原發件數、原發重量及驗收重量的統計報表。其中，原發件數、原發重量和驗收重量等列要求計算出各等級組別的小計和所有記錄的合計。

2.2.    我們通常的做法

1.用下面的SQL統計出初步的資料集。

 

查詢的結果如下表所示

菸葉等級	等級組別	原發件數	原發重量	驗收重量
(B1F)上桔一	51	4945	197800	197508.1
(B2F)上桔二	51	8335	333400	332316.9
(C1F)中桔一	51	694	27760	27610.54
(C2F)中桔二	51	803	32120	31650.4
(C3F)中桔三	51	6381	255240	255372.6
(X1F)下桔一	51	75	3000	3012.2
(B3F)上桔三	52	4701	188040	187389.9
(B4F)上桔四	52	122	4880	4866.81
(C3V)中微青三	52	174	6960	6934.06
(C4F)中桔四	52	4639	185560	185276.4
(X2F)下桔二	52	1739	69560	69029.79
(X2V)下微青二	52	26	1040	1038.34
(X3F)下桔三	52	1263	50520	50439.86
(X4F)下桔四	53	102	4080	4075.62
(B3K)上雜三	54	0	0	249.39

                            

2.再在程式中編寫相應的方法對查詢得到的資料集進行處理。 我們的重點不是在怎麼寫處理資料集的方法上，所以相應的方法在此略去。

 

 

2.3.      用SQL直接實現的方法

 

  

透過查詢得到統計結果如下表所示，該表的統計結果已經滿足了統計要求。

菸葉等級	等級組別	原發件數	原發重量	驗收重量
(B1F)上桔一	51	4945	197800	197508.1
(B2F)上桔二	51	8335	333400	332316.9
(C1F)中桔一	51	694	27760	27610.54
(C2F)中桔二	51	803	32120	31650.4
(C3F)中桔三	51	6381	255240	255372.6
(X1F)下桔一	51	75	3000	3012.2
上等煙小計	51	21233	849320	847470.8
(B3F)上桔三	52	4701	188040	187389.9
(B4F)上桔四	52	122	4880	4866.81
(C3V)中微青三	52	174	6960	6934.06
(C4F)中桔四	52	4639	185560	185276.4
(X2F)下桔二	52	1739	69560	69029.79
(X2V)下微青二	52	26	1040	1038.34
(X3F)下桔三	52	1263	50520	50439.86
中等煙小計	52	12664	506560	504975.1
(X4F)下桔四	53	102	4080	4075.62
下等煙小計	53	102	4080	4075.62
(B3K)上雜三	54	0	0	249.39
低等煙小計	54	0	0	249.39
合計		33999	1359960	1356771

 

 

    透過比較，相信大家也感覺到後者的獨特魅力了吧。至少我在寫那些對資料集處理得到小計行的方法的時候，感到雖不是很複雜，但也是有些繁瑣的，遠不如直接在SQL中實現小計來得瀟灑、簡潔。

感謝作者，文章轉自：http://www.cnblogs.com/hx8023zx/archive/2012/01/09/2317028.html