多级分类汇总

实例

 select  FHeadSelfB0940	工程项目,
v2.Fname  部门,v1.FNumber as 物料代码,v1.FName 物料名称,v1.FModel 规格,t4.FName 计量单位,sum(t1.FQty) 数量,sum(t1.FAmount) 金额
  from icstockbillentry t1
 left join icstockbill t2 on t1.Finterid=t2.Finterid   
 left join t_icitemcore v1 on t1.FItemID=v1.FItemID
left join t_Department v2 on t2.FDeptID=v2.FItemID
left join t_measureunit t4 on t1.FUnitID = t4.fitemid
 where FTrantype=29
 group by FHeadSelfB0940,v2.Fname,v1.FNumber,v1.FName,v1.FModel,t4.FName  
 ORDER BY FHeadSelfB0940,v2.Fname 
 --with rollup having not (  grouping(v1.FNumber) =1 or grouping(v1.FName) = 1 OR GROUPING(v1.FModel)=1 or GROUPING(t4.FName)=1  )
 --or grouping(FHeadSelfB0940)=1 or grouping(v2.Fname)=1 
COMPUTE SUM(SUM(t1.FQty)),sum(sum(t1.FAmount)) BY FHeadSelfB0940,v2.Fname 
COMPUTE SUM(SUM(t1.FQty)),sum(sum(t1.FAmount))

　　多级分类汇总

select case when grouping(t5.fname) = 1 then '总计'  else case when grouping(t3.fnumber) = 1 then ''+ t5.fname + '小计' else t5.fname end end  as 业务员,
w1.fbillno as 采购申请单号,sum(w2.fqty) as 申请数量,t1.fbillno as 采购订单号,t3.fnumber as 物料代码,
t3.fname as 物料名称,t3.fmodel as 规格,t4.fname as 单位,cast(sum(t2.fqty) as decimal(10,2)) as 订单数量,cast(sum(x2.fqty + isnull(x4.fqty,0))   as decimal(10,2)) as 入库数量 ,case when sum(t2.fqty - isnull(x2.fqty,0) + isnull(x4.fqty,0))< 0 then 0 else cast(sum(t2.fqty - isnull(x2.fqty,0) + isnull(x4.fqty,0)) as decimal(10,2)) end  as 订单余量 ,
t2.fdate as 计划交货日期,case when t1.fstatus = 3 then max(x3.fdate) else null end as 实际结案日期,cast(sum(x2.famount)/sum(x2.fqty)  as decimal(10,2)) as 单价,cast(sum(x2.famount) as decimal(10,2)) as 金额,w3.fname as 供应商
from poorder t1 inner join poorderentry t2 on t1.finterid = t2.finterid
inner join t_icitem t3 on t2.fitemid = t3.fitemid inner join t_measureunit t4 on t3.funitid = t4.fitemid
left join t_emp t5 on t1.fempid = t5.fitemid
left join porequest w1 on t2.FSourceInterId = w1.finterid
left join porequestentry w2 on w1.finterid = w2.finterid and   t2.FSourceEntryID = w2.fentryid
left join t_supplier w3 on t1.fsupplyid = w3.fitemid
left join icstockbillentry x2 on x2.fsourceinterid = t2.finterid and x2.fsourceentryid = t2.fentryid and x2.fsourcetrantype = 71
left join icstockbill x3 on x2.finterid = x3.finterid
left join icstockbillentry x4 on  x4.fsourceinterid = x2.finterid and x4.fsourceentryid = x2.fentryid  and x4.fsourcetrantype = 1
--where (t3.fnumber = @winumber or @winumber='') and (t5.fname = @wemp or @wemp='') and  (w3.fname = @wsupply or @wsupply='') and ((t1.fdate BETWEEN @date1 and @date2  and x2.fqty <>0 ) or (t1.fstatus in(1,2) and t1.fdate <  dateadd(month,-3,getdate())))
group by t5.fname,w1.fbillno,t1.fbillno,t3.fnumber,t3.fname,t3.fmodel,t4.fname,t2.fdate,w3.fname,t1.fstatus
with rollup
having not(  grouping(t3.fnumber) =1 or grouping(t3.fname) = 1 or grouping(t3.fmodel) = 1
 or grouping(t4.fname) = 1 or grouping (t2.fdate) = 1 or grouping(w3.fname)=1 or grouping(t1.fstatus) =1)
or grouping(t5.fname) = 1 or grouping(w1.fbillno) =1 or grouping(t1.fbillno) = 1

　　

 select case when grouping(FHeadSelfB0940) = 1 then '总计'   
			else case when grouping(v2.Fname)=1 THEN ''+ v2.Fname + '小计' 
			else FHeadSelfB0940	end 
		end  as 工程项目,
v2.Fname  部门,v1.FNumber as 物料代码,v1.FName 物料名称,v1.FModel 规格,t4.FName 计量单位,sum(t1.FQty) 数量,sum(t1.FAmount) 金额
  from icstockbillentry t1
 left join icstockbill t2 on t1.Finterid=t2.Finterid   
 left join t_icitemcore v1 on t1.FItemID=v1.FItemID
left join t_Department v2 on t2.FDeptID=v2.FItemID
left join t_measureunit t4 on t1.FUnitID = t4.fitemid
 where FTrantype=29
 group by FHeadSelfB0940,v2.Fname,v1.FNumber,v1.FName,v1.FModel,t4.FName  with rollup
 having not (  grouping(v1.FNumber) =1 or grouping(v1.FName) = 1 OR GROUPING(v1.FModel)=1 or GROUPING(t4.FName)=1  )
 or grouping(FHeadSelfB0940)=1 or grouping(v2.Fname)=1