表: 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
------解决方案--------------------
------解决方案--------------------
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
------解决方案--------------------
二樓的不就是對的嘛
------解决方案--------------------