日期:2014-05-18  浏览次数:20593 次

感谢昨天论坛朋友帮忙,经昨晚修改,已能实现!!!
SQL code

/*
感谢      晴天、小F、中国风、大乌龟 [img=http://hi.csdn.net/attachment/201108/17/4981413_1313567397Jdb8.gif][/img]
谢谢你们给的思路,我不是搞数据库的,我是做软件实施的,之前可能接触的比较少,因为我们实施一直提倡尽量不写
SQL、存储过程来实现。
*/

--昨天根据各位的思路,已经实现了:
Select 
  a.KJND,
  a.GSDM,
  a.YSDWDM,
  a.M1,
  L1=SUM(CASE WHEN (ZBLYDM like '0601%'or ZBLYDM like '0501%' or ZBLYDM like '0401%') THEN JE ELSE 0 END), 
  a.M2,
  L2=SUM(CASE WHEN (ZBLYDM like '0601%') THEN JE ELSE 0 end),
  a.M3,
  L3=SUM(CASE WHEN (ZBLYDM like '0401%'or ZBLYDM like '0501%') THEN JE ELSE 0 end)
from 
(Select 
  KJND,
  GSDM,
  YSDWDM,
  M1=SUM(CASE WHEN (ZBLYDM like '0601%'or ZBLYDM like '0501%' or ZBLYDM like '0401%') THEN JE ELSE 0 END), 
  M2=SUM(CASE WHEN (ZBLYDM like '0601%') THEN JE ELSE 0 end),
  M3=SUM(CASE WHEN (ZBLYDM like '0401%'or ZBLYDM like '0501%') THEN JE ELSE 0 end)
from ZB_MXZB AS a
WHERE 
(ZBZT<>'2') and (substring(sh_rq,1,6)='201109') and (shr_id<>-1) and (ysdwdm like '117%')  
GROUP BY KJND,GSDM,YSDWDM) a,zb_mxzb as b
WHERE a.KJND=b.KJND AND a.GSDM=b.GSDM AND a.YSDWDM=b.YSDWDM and b.sh_rq<=20110930
GROUP BY a.KJND,a.GSDM,a.YSDWDM,a.M1,a.M2,a.M3
KJND GSDM YSDWDM M1 L1 M2 L2 M3 L3
2011 888 117001 37669592.00 318602290.30 29375180.00 232324988.00 8294412.00 86277302.30
2011 888 117002 33334014.00 478225902.60 26787275.00 287008885.00 6546739.00 191217017.60
2011 888 117003 35730992.00 199975184.80 19444260.00 139101488.00 16286732.00 60873696.80
2011 888 117004 61451809.70 458402874.00 51297855.70 407558488.70 10153954.00 50844385.30
2011 888 117005 186481700.00 923415293.90 157751860.00 840646495.00 28729840.00 82768798.90
2011 888 117006 127937923.00 607431521.60 103272080.00 534468376.00 24665843.00 72963145.60
2011 888 117007 12818722.00 203163593.30 11530820.00 186256837.00 1287902.00 16906756.30


------解决方案--------------------
不谢不谢~~
------解决方案--------------------
不谢不谢~~ 
 

------解决方案--------------------
恭喜.

主要是不清楚你的需求,所以未能全帮.
------解决方案--------------------
感谢分享.
------解决方案--------------------
SQL code
Select  
  a.KJND,
  a.GSDM,
  a.YSDWDM,
  a.M1,
  L1=SUM(CASE WHEN (ZBLYDM like '0601%'or ZBLYDM like '0501%' or ZBLYDM like '0401%') THEN JE ELSE 0 END),  
  a.M2,
  L2=SUM(CASE WHEN (ZBLYDM like '0601%') THEN JE ELSE 0 end),
  a.M3,
  L3=SUM(CASE WHEN (ZBLYDM like '0401%'or ZBLYDM like '0501%') THEN JE ELSE 0 end)
from  
(Select  
  KJND,
  GSDM,
  GSMC,
  YSDWDM,
  M1=SUM(CASE WHEN (ZBLYDM like '0601%'or ZBLYDM like '0501%' or ZBLYDM like '0401%') THEN JE ELSE 0 END),  
  M2=SUM(CASE WHEN (ZBLYDM like '0601%') THEN JE ELSE 0 end),
  M3=SUM(CASE WHEN (ZBLYDM like '0401%'or ZBLYDM like '0501%') THEN JE ELSE 0 end)
  from ZB_MXZB AS a
  WHERE  
  (ZBZT<>'2') and (substring(sh_rq,1,6)='201109') and (shr_id<>-1) and (ysdwdm like '117%')   
  GROUP BY KJND,GSDM,GSMC,YSDWDM
)a,zb_mxzb as b
WHERE a.KJND=b.KJND AND a.GSDM=b.GSDM AND a.YSDWDM=b.YSDWDM and b.sh_rq<=20110930
GROUP BY a.KJND,a.GSDM,a.GSMC,a.YSDWDM,a.M1,a.M2,a.M3

------解决方案--------------------
求合计:
SQL code

select *,M1+L1+M2+L2 as 合计 from(
Select  
  a.KJND,
  a.GSDM,
  a.YSDWDM,
  a.M1,
  L1=SUM(CASE WHEN (ZBLYDM like '0601%'or