--第一步：根據系統編號、列、單價分組求和
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
?
?--第二步：列轉行,分組求和
?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 ????????????????????
--第三步：求最終結果
select MC,GG,DW,DJ,SYSL,SYJE,RKSL,RKJE,CKSL,CKJE,BYSL,BYJE
from #TempLastResults inner join
(
?SELECT '0' AS BH,'全部材料總計' AS MC,'' AS GG,'' AS DW
?UNION ALL
?SELECT BH,(MC+'? 合計') AS MC,GG,DW FROM CaiLiaoXinxi WHERE SJ='0' and MJ=0
?UNION ALL SELECT BH,(MC+'? 小計') 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
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
