sql 分组小计与合计语法

Question

我想做一个分组的小计与合计，发现有一个问题
初始代码：select case when grouping(oil_name)=1 and grouping(oildepot_name)=0 then N'小计' else isnull(oildepot_name,'') end '出油库',case when grouping(oildepot_name)=1 then N'合计' else isnull(oil_name,'') end '油品',sum(qty) as '数量'from tb_Tally_SaleReceivables where unit_name='吨' group by oildepot_name,oil_name with rollup

显示的数据很正常
但是 当我加上单位一列之后：
select case when grouping(oil_name)=1 and grouping(oildepot_name)=0 then N'小计' else isnull(oildepot_name,'') end '出油库',case when grouping(oildepot_name)=1 then N'合计' else isnull(oil_name,'') end '油品',unit_name as '单位',sum(qty) as '数量'from tb_Tally_SaleReceivables where unit_name='吨' group by oildepot_name,oil_name,unit_name with rollup

就会多出一行小计行,问题可能出现在group by分组里面,现在我想问一下,怎么样才能避免图2的问题,就是说不管我怎么分组,小计行永远只会有一条 希望高手解答,高分送上

Answer 1

group by oildepot_name,oil_name with rollup
改成
group by rollup(oildepot_name),oil_name

Answer 2

看图就知道是没办法合组呗，将NULL值ISNULL一下，看你这单位也没有第二种，直接就null时为吨，自然就能合组了呗

Answer 3

你解决的话能把代码贴上来吗？学习一下，谢谢

追问

with rollup having ((grouping(oil_name)=0 and grouping(oildepot_name)=0 and grouping(unit_name)=0) or ( grouping(oil_name)=1 and grouping(oildepot_name)=0 and grouping(unit_name)=1 ) )
加上条件就好了