1.rollup会对每个分组进行合计,如:
select
t.a13 as product,
t.a17 as businessline,
sum(t.par_bal) par_bal
from rp_port t
where substr(t.a13, 1, 1) in (1) and rownum<15
group by rollup(t.a13,t.a17)
结果:
product | businessline | parbal |
320245274.4 | ||
1010101001 | 201800000 | |
1010101001 | 11 | 201800000 |
1010101008 | 97430.25 | |
1010101008 | 11 | 97430.25 |
1010102001 | 30000000 | |
1010102001 | 11 | 30000000 |
1010102002 | 75000000 | |
1010102002 | 11 | 75000000 |
1010102003 | 5096444.11 | |
1010102003 | 12 | 5096444.11 |
1020302000 | 8251400 | |
1020302000 | 41 | 8251400 |
rollup(parm1,parm2...)中,以parm1的角度看分组统计,如:
select
t.a13 as product,
t.a17 as businessline,
sum(t.par_bal) par_bal
from rp_port t
where substr(t.a13, 1, 1) in (1) and rownum<15
group by rollup(t.a17,t.a13)
结果:
product | businessline | parbal |
320245274.4 | ||
11 | 306897430.3 | |
1010101001 | 11 | 201800000 |
1010101008 | 11 | 97430.25 |
1010102001 | 11 | 30000000 |
1010102002 | 11 | 75000000 |
12 | 5096444.11 | |
1010102003 | 12 | 5096444.11 |
41 | 8251400 | |
1020302000 | 41 | 8251400 |
2.cube即展示出交叉表的效果,cube(parm1,parm2...)参数位置没有影响,如:
select
t.a13 as product,
t.a17 as businessline,
sum(t.par_bal) par_bal
from rp_port t
where substr(t.a13, 1, 1) in (1) and rownum<15
group by cube(t.a17,t.a13)
结果:
product | businessline | parbal |
320245274.4 | ||
1010101001 | 201800000 | |
1010101008 | 97430.25 | |
1010102001 | 30000000 | |
1010102002 | 75000000 | |
1010102003 | 5096444.11 | |
1020302000 | 8251400 | |
11 | 306897430.3 | |
1010101001 | 11 | 201800000 |
1010101008 | 11 | 97430.25 |
1010102001 | 11 | 30000000 |
1010102002 | 11 | 75000000 |
12 | 5096444.11 | |
1010102003 | 12 | 5096444.11 |
41 | 8251400 | |
1020302000 | 41 | 8251400 |