实例
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