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)

 结果：                   

productbusinesslineparbal  320245274.41010101001 2018000001010101001112018000001010101008 97430.2510101010081197430.251010102001 30000000101010200111300000001010102002 75000000101010200211750000001010102003 5096444.111010102003125096444.111020302000 82514001020302000418251400

 

 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)

 结果： 

productbusinesslineparbal  320245274.4 11306897430.310101010011120180000010101010081197430.251010102001113000000010101020021175000000 125096444.111010102003125096444.11 4182514001020302000418251400

 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)

 结果：

 

productbusinesslineparbal320245274.41010101001201800000101010100897430.2510101020013000000010101020027500000010101020035096444.111020302000825140011306897430.310101010011120180000010101010081197430.251010102001113000000010101020021175000000125096444.111010102003125096444.114182514001020302000418251400