Oracle ROLLUP和CUBE 用法

CUBE和ROLLUP  ROLLUP是Oracle8i以來一個新的SQL關鍵字，9i ,-11G不新鮮了，它是對GROUP BY子句的擴充，允許在SELECT語句中計算不同層次的小計。

CUBE是對GROUP BY子句的另外一  種擴充，它允許計算所有小計可能的組合，用來產生各種匯總報表。

以下介紹他們的用法

select B,c,D from Atest group by rollup(B,C,D);

相當於

select B,c,D from Atest group by B,C,D;

union all

select B,c,null from Atest group by B,C

union all

select B,null,null from Atest group by B

select B,c from Atest group by cube(B,C);

將所有 (B,C)組合的子集group by合并

相當於

select B,c from Atest group by B,C

union all

select B,null from Atest group by B

union all

select null,c from Atest group by c

union all

select null,null from Atest group by null