感谢昨天论坛朋友帮忙，经昨晚修改，已能实现！该怎么处理

感谢昨天论坛朋友帮忙，经昨晚修改，已能实现！！！

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 888117001 37669592.00 318602290.30 29375180.00 232324988.00 8294412.00 86277302.30
2011 888117002 33334014.00 478225902.60 26787275.00 287008885.00 6546739.00 191217017.60
2011 888117003 35730992.00 199975184.80 19444260.00 139101488.00 16286732.00 60873696.80
2011 888117004 61451809.70 458402874.00 51297855.70 407558488.70 10153954.00 50844385.30
2011 888117005 186481700.00 923415293.90 157751860.00840646495.00 28729840.00 82768798.90
2011 888117006 127937923.00 607431521.60 103272080.00534468376.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 bWHERE a.KJND=b.KJND AND a.GSDM=b.GSDM AND a.YSDWDM=b.YSDWDM and b.sh_rq<=20110930GROUP 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 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 bWHERE a.KJND=b.KJND AND a.GSDM=b.GSDM AND a.YSDWDM=b.YSDWDM and b.sh_rq<=20110930GROUP BY a.KJND,a.GSDM,a.GSMC,a.YSDWDM,a.M1,a.M2,a.M3)T 
 
[解决办法]
"1.我想在另张表pubgszl里提取单位名称进去，老是有错- -"
Please try below.
SQL code
Select    a.KJND,  a.GSDM,  a.YSDWDM,  c.gsmc  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 aWHERE  (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,pubgszl as cWHERE a.KJND=b.KJND AND a.GSDM=b.GSDM AND a.YSDWDM=b.YSDWDM and b.sh_rq<=20110930 and b.ysdwdm=c.gsdmGROUP BY a.KJND,a.GSDM,a.YSDWDM,a.M1,a.M2,a.M3
[解决办法]
对了,还少写一个列名:
SQL code
Select    a.KJND,  a.GSDM,  a.GSMC  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 bWHERE a.KJND=b.KJND AND a.GSDM=b.GSDM AND a.YSDWDM=b.YSDWDM and b.sh_rq<=20110930GROUP BY a.KJND,a.GSDM,a.GSMC,a.YSDWDM,a.M1,a.M2,a.M3
[解决办法]
上面的大佬们都写出来了 我就围观一下算了
[解决办法]
生成CTE可直接用 group by +rollup 

 

查看更多 下一篇