SQL使用ROLLUP计算多级汇总时如何在总计行添加Collector名称

核心调整点 #

仅需调整Collector列的CASE判断逻辑即可，原逻辑是所有汇总行（Rn的grouping值为1）都返回空值，修改为只有Grand Total行保留Collector名称，其他汇总行（SubTotal、Product Total）保持为空，如果需要SubProduct列的汇总描述也带人员名称可以同步调整该列逻辑。

修改后完整代码 #

declare @t table(Collector varchar(50),Product varchar(50),SubProduct varchar(50),Amount int)

Insert into @t 
select 'Vijay','NCB','Credit Card',8000 union all
select 'Vijay','NCB','Credit Card',2000 union all
select 'Vijay','NCB','Credit Card',17000 union all
select 'Vijay','NCB','Credit Card',5000 union all
select 'Vijay','NCB','Loan',15000 union all
select 'Vijay','NCB','Loan',5000 union all
select 'Amreet','NCB','Credit Card',3000 union all
select 'Amreet','NCB','Credit Card',1000 union all
select 'Amreet','NCB','Loan',45000 union all
select 'Amreet','NCB','Loan',9000  union all 
select 'Amreet','NCT','Loan',1000 union all
select 'Amreet','NCT','Loan',2000 

Select 
*
from 

(select 
  -- 核心修改：Grand Total行保留Collector名称
  case 
    when grouping(Product) = 1 and grouping(SubProduct) = 1 and grouping(Rn) = 1 then Collector
    when grouping(Rn) = 1 then '' 
    else Collector end as Collector,

  case when grouping(Rn) = 1 then '' else Product end as Product,
  
  case 
    when grouping(Collector) = 0 and grouping(Product) = 1 and grouping(SubProduct) = 1 and grouping(Rn) = 1 then Collector + ' Grand Total' -- 可选：汇总描述也带人员名称
    when grouping(Collector) = 0 and grouping(Product) = 0 and grouping(SubProduct) = 1 and grouping(Rn) = 1 then 'Total(Product Total)' 
    when grouping(Collector) = 0 and grouping(Product) = 0 and grouping(SubProduct) = 0 and grouping(Rn) = 1 then 'SubTotal(Sub Product Total)' 
    else SubProduct end as SubProduct,

  sum(Amount) as Amount

from

(select 
    *, 
    Rn = row_number() over(partition by Collector,Product,SubProduct order by 1/0)  
 from @t) tData 
group by
  rollup(Collector,Product,SubProduct, Rn))x
 
 where x.SubProduct is not null

效果说明 #

调整后每个Collector的Grand Total行的Collector列会显示对应人员名称，若使用了可选修改方案，SubProduct列也会显示类似Amreet Grand Total的描述，完全符合需求。

内容的提问来源于stack exchange，提问作者priyanka.sarkar