分析函式rollup||cube學習

對rollup,對資料合計的功能

1 10
1 22
1 31
2 40

select nvl(line,'合計') line ,sum(value) from kk
group by rollup(nvl(line,'合計'))
order by line

RESULT：

1 63
2 40
103

cube學習:

select * from kk

line value ko

1 10 A
1 22 A
1 31 B
2 40 B

--cube

select line,k0,sum(value)
from kk
group by cube(line,k0)
order by line

RESULT:

1 1 A 32
2 1 B 31
3 1 63
4 2 B 40
5 2 40
6 A 32
7 B 71
8 103

1.增加按line 彙總

2.按ko彙總

3.總合計

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

加入grouping函式：

當前彙總記錄是利用該欄位得出，返回1，否則為0

select decode(grouping(line),1,'月度',to_char(line)) line,
decode(grouping(k0),1'部門',to_char(k0)) k0,sum(value)
from kk
group by cube(line,k0)
order by line,k0

[@more@]