selectcasewhengrouping(a)=1then'合计'elsecast(a asvarchar) end a, sum(b),sum(c),sum(d),sum(e) from #t groupby a with rollup
情况二:
有多个分类汇总列,只需要一个合计.增加rollup之后,需要增加判断。
selectcasewhengrouping(a)=1then'合计'elsecast(a asvarchar) end a, b, sum(c),sum(d),sum(e) from #t groupby a,b with rollup havinggrouping(b)=0orgrouping(a)=1
selectcasewhengrouping(a)=1then'合计'elsecast(a asvarchar) end a, b, c, sum(d),sum(e) from #t groupby a,b,c with rollup havinggrouping(c)=0orgrouping(a)=1
情况三:
有多个分类汇总列,需要全部的小计和合计。
selectcasewhengrouping(a)=1then'合计'elsecast(a asvarchar) end a, casewhengrouping(b)=1andgrouping(a)=0then'小计'elsecast(b asvarchar) end b, casewhengrouping(c)=1andgrouping(b)=0then'小计'elsecast(c asvarchar) end c, sum(d),sum(e) from #t groupby a,b,c with rollup
另外一种显示小计的方式
selectcasewhengrouping(a)=1then'合计' whengrouping(b)=1thencast(a asvarchar)+'小计' elsecast(a asvarchar) end a, casewhengrouping(b)=0andgrouping(c)=1 thencast(b asvarchar)+'小计'elsecast(b asvarchar) end b, casewhengrouping(c)=1andgrouping(b)=0 then''elsecast(c asvarchar) end c, sum(d),sum(e) from #t groupby a,b,c with rollup
情况四:
有多个分类汇总列,需要部分的小计和合计
selectcasewhengrouping(a)=1then'合计'elsecast(a asvarchar) end a, b, casewhengrouping(c)=1andgrouping(b)=0then'小计'elsecast(c asvarchar) end c, sum(d),sum(e) from #t groupby a,b,c with rollup havinggrouping(a)=1orgrouping(b)=0
selectcasewhengrouping(a)=1then'合计'elsecast(a asvarchar) end a, casewhengrouping(b)=1andgrouping(a)=0then'小计'elsecast(b asvarchar) end b, c, sum(d),sum(e) from #t groupby a,b,c with rollup havinggrouping(a)=1orgrouping(b)=1orgrouping(c)=0