sql-server - 总计的总和

Question

我有以下要求

可以看出，每当 SubProduct 发生变化时，对于每个 Collector，我们都在执行它的“SubTotal”。

一旦“SubTotal”结束，我们将执行“Product Total”，它是该收集器在 Product 级别的“SubProducts”总和（意味着直到 Product 被更改）。

最后是那个收藏品的“Grand Total”

例如 Collector Amreet 有 2 个产品（NCB 和 NCT）。对于 NCB，他有 2 个子产品，即。信用卡和贷款。他的产品总计在 NCB 和 NCT 级别以及所有产品的总计中计算。

收藏家“维杰”也是如此

我编写了以下代码，非常接近要求，唯一的问题是，我无法在总级别添加/附加收集器名称。

到目前为止我的尝试

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 
    
  case 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 '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

输出

标签： sql-servertsql

Answer 1

请检查这个。在这里，我添加了一件事。

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 
    
  case 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 'Grand Total (' + Collector + ')'
    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;