--第一步：根據(jù)系統(tǒng)編號(hào)、列、單價(jià)分組求和

select CLBH,DJ,sum(SL) as SL,sum(JE) as JE,Lie

??????????????????????????? into #TempSZCMX

?from #ShouZhiCunMingXi group by Lie, CLBH , DJ order by Lie,CLBH

?

?--第二步：列轉(zhuǎn)行,分組求和

?insert into #TempLastResults

SELECT YJ,EJ,

???????? MoJi=(CASE WHEN grouping(YJ) = 1 THEN '0'?

??????????????????????????? WHEN grouping(EJ) = 1 THEN [YJ]

??????????????????????????? when grouping(MoJi)=1 then [EJ] else MoJi END),

? DJ,

sum(SYSL) SYSL,SUM(SYJE) SYJE,SUM(BYSL) BYSL,SUM(BYJE) BYJE,

SUM(CKSL) CKSL,SUM(CKJE) CKJE,SUM(RKSL) RKSL,SUM(RKJE) RKJE

--into #TempLastResults

from

(

?

select CaiLiaoXinxi_1.SJ AS YJ,CaiLiaoXinxi_1.BH AS EJ,CaiLiaoXinxi.BH AS MoJi,DJ,

sum(case Lie when '上月' then SL else 0 end) as 'SYSL',

??????????????????????????? sum(case Lie when '上月' then JE else 0 end) as 'SYJE',

??????????????????????????? sum(case Lie when '本月' then SL else 0 end) as 'BYSL',

??????????????????????????? sum(case Lie when '本月' then JE else 0 end) as 'BYJE',

??????????????????????????? sum(case Lie when '出庫' then SL else 0 end) as 'CKSL',

??????????????????????????? sum(case Lie when '出庫' then JE else 0 end) as 'CKJE',

??????????????????????????? sum(case Lie when '入庫' then SL else 0 end) as 'RKSL',

??????????????????????????? sum(case Lie when '入庫' then JE else 0 end) as 'RKJE'

??????????????????????????? from #TempSZCMX inner join CaiLiaoXinxi ON #TempSZCMX.CLBH =CaiLiaoXinxi.XTBH

??????????????????????????? INNER JOIN CaiLiaoXinxi? CaiLiaoXinxi_1 ON CaiLiaoXinxi.SJ=CaiLiaoXinxi_1.BH

??????????????????????????? GROUP BY CaiLiaoXinxi.BH ,CaiLiaoXinxi_1.BH,CaiLiaoXinxi_1.SJ,DJ

??????????????????????????? ) as MingXi

??????????????????????????? group by YJ,EJ,MoJi,DJ with rollup? having grouping(MoJi) =1 union all???????

select CaiLiaoXinxi_1.SJ AS YJ,CaiLiaoXinxi_1.BH AS EJ,CaiLiaoXinxi.BH AS MoJi,DJ,

sum(case Lie when '上月' then SL else 0 end) as 'SYSL',

??????????????????????????? sum(case Lie when '上月' then JE else 0 end) as 'SYJE',

??????????????????????????? sum(case Lie when '本月' then SL else 0 end) as 'BYSL',

??????????????????????????? sum(case Lie when '本月' then JE else 0 end) as 'BYJE',

??????????????????????????? sum(case Lie when '出庫' then SL else 0 end) as 'CKSL',

??????????????????????????? sum(case Lie when '出庫' then JE else 0 end) as 'CKJE',

??????????????????????????? sum(case Lie when '入庫' then SL else 0 end) as 'RKSL',

??????????????????????????? sum(case Lie when '入庫' then JE else 0 end) as 'RKJE'

??????????????????????????? --into #TempLastResults

??????????????????????????? from #TempSZCMX inner join CaiLiaoXinxi ON #TempSZCMX.CLBH =CaiLiaoXinxi.XTBH

??????????????????????????? INNER JOIN CaiLiaoXinxi? CaiLiaoXinxi_1 ON CaiLiaoXinxi.SJ=CaiLiaoXinxi_1.BH

??????????????????????????? GROUP BY CaiLiaoXinxi.BH ,CaiLiaoXinxi_1.BH,CaiLiaoXinxi_1.SJ,DJ ????????????????????

--第三步：求最終結(jié)果

select MC,GG,DW,DJ,SYSL,SYJE,RKSL,RKJE,CKSL,CKJE,BYSL,BYJE

from #TempLastResults inner join

(

?SELECT '0' AS BH,'全部材料總計(jì)' AS MC,'' AS GG,'' AS DW

?UNION ALL

?SELECT BH,(MC+'? 合計(jì)') AS MC,GG,DW FROM CaiLiaoXinxi WHERE SJ='0' and MJ=0

?UNION ALL SELECT BH,(MC+'? 小計(jì)') AS MC,GG,DW FROM CaiLiaoXinxi WHERE SJ<>'0' AND MJ=0

?UNION ALL SELECT BH, MC,GG,DW FROM CaiLiaoXinxi WHERE MJ=1

?) AS B on B.BH=#TempLastResults.MoJi and B.BH like @LeiBie order by B.BH

sql材料分級(jí)統(tǒng)計(jì)及匯總案例參考