rollup与cube

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