条件求和解决方法

条件求和
表： SFD_2
列： ZYH, KZYS,SFLB,SFBZBH,DJ 等

原来查询：

select ZYH,KZYS YS,len(SFBZBH) LB,sum(SJFY) HZJE from SFD_2  
where convert(char(8),RQ,112)>='20120701' group by FSKS, len(SFBZBH) ,KZYS,ZYH 

现在要加一个对 SJFY 的求和，是计算 SFLB='1501' and DJ>=1500 。

最好是不改变原来查询的 where ，这样就行：

select ZYH,KZYS YS,len(SFBZBH) LB,sum(SJFY) HZJE , 新求和 from SFD_2  
where convert(char(8),RQ,112)>='20120701' group by FSKS, len(SFBZBH) ,KZYS,ZYH

[解决办法]

SQL code

select ZYH,       KZYS as YS,       len(SFBZBH) as LB,       (select sum(SJFY) from  SFD_2 where SFLB='1501' and DJ>=1500  )as HZJE from SFD_2   where convert(char(8),RQ,112)>='20120701' group by FSKS, len(SFBZBH) ,KZYS,ZYH
[解决办法]
SQL code
SELECT  ZYH ,        KZYS YS ,        LEN(SFBZBH) LB ,        SUM(SJFY) HZJE,        sum(CASE WHEN SFLB='1501' and DJ>=1500 THEN sjfy ELSE 0 END ) AS 新求和FROM    SFD_2WHERE   CONVERT(CHAR(8), RQ, 112) >= '20120701'GROUP BY FSKS ,        LEN(SFBZBH) ,        KZYS ,        ZYH
[解决办法]
嵌套子查询即可
[解决办法]
SQL code
select ZYH,       KZYS as YS,       len(SFBZBH) as LB,       SUM(SJFY) HZJE,       (select sum(SJFY) from  SFD_2 where SFLB='1501' and DJ>=1500  )as 新求和from SFD_2   where convert(char(8),RQ,112)>='20120701' group by FSKS, len(SFBZBH) ,KZYS,ZYH
[解决办法]
SQL code
select ZYH,KZYS YS,len(SFBZBH) LB,sum(case when SFLB='1501' and DJ>=1500 then SJFY else 0 end) HZJE from SFD_2   where convert(char(8),RQ,112)>='20120701' group by FSKS, len(SFBZBH) ,KZYS,ZYH
[解决办法]
新求和也要出现在group by中，忘记加了
[解决办法]
SQL code
--tryselect ZYH,       KZYS as YS,       len(SFBZBH) as LB,       SUM(SJFY) HZJE,       SUM(select SJFY from  SFD_2 where SFLB='1501' and DJ>=1500  )as 新求和from SFD_2   where convert(char(8),RQ,112)>='20120701' group by FSKS, len(SFBZBH) ,KZYS,ZYH
[解决办法]
探讨


引用:
SQL code

select ZYH,
KZYS as YS,
len(SFBZBH) as LB,
SUM(SJFY) HZJE,
(select sum(SJFY) from  SFD_2 where SFLB='1501' and DJ>=1500  )as 新求和
from SFD_2

where con……




结果不正……

[解决办法]
LZ这个意思？
SQL code
select ZYH,       KZYS as YS,       len(SFBZBH) as LB,       SUM(SJFY) HZJE,       SUM(select SJFY from  SFD_2 where SFLB='1501' and DJ>=1500 AND KZYS = A.KZYS)as 新求和from SFD_2  AS A  where convert(char(8),RQ,112)>='20120701' group by FSKS, len(SFBZBH) ,KZYS,ZYH
[解决办法]
二樓的不就是對的嘛
[解决办法]
探讨


引用:
LZ这个意思？

SQL code

select ZYH,
KZYS as YS,
len(SFBZBH) as LB,
SUM(SJFY) HZJE,
SUM(select SJFY from  SFD_2 where SFLB='1501' and DJ>=1500 AND KZYS = A.KZYS)as 新求和
f……


是的，但……