现在要根据case中条件的不同进行进行sum
Select CASE
WHEN YF_ZYFYMX.YFDW=YK_TYPK.BFDW THEN SUM(YK_YPCD.JHJG/YK_TYPK.YFBZ*YF_ZYFYMX.YPSL)
WHEN YF_ZYFYMX.YFDW<>YK_TYPK.BFDW THEN SUM(YK_YPCD.JHJG*YF_ZYFYMX.YPSL)
END AS JHJE
From YF_ZYFYMX , YK_YPCD,YK_TYPK Where
YF_ZYFYMX.YFSB =3 And YF_ZYFYMX.YPXH =YK_YPCD.YPXH
And YF_ZYFYMX.YPCD =YK_YPCD.YPCD And YF_ZYFYMX.JFRQ > '2011-4-30 23:59:59.000'
And YF_ZYFYMX.JFRQ <= '2011-5-31 23:59:59.000' And YF_ZYFYMX.YFSB =3;
但是这样写却无法执行,是错在哪里?该怎么修正?
这条SQL语句就是根据case中条件的不同,进行sum求和,但是求和出来的结果在同一列。
Select sum ( YF_ZYFYMX.YPSL *YK_YPCD.JHJG ) From YF_ZYFYMX ,
YK_YPCD Where YF_ZYFYMX.YFSB =3 And YF_ZYFYMX.YPXH =YK_YPCD.YPXH
And YF_ZYFYMX.YPCD =YK_YPCD.YPCD And YF_ZYFYMX.JFRQ > '2011-4-30 23:59:59.000'
And YF_ZYFYMX.JFRQ <= '2011-5-31 23:59:59.000' And YF_ZYFYMX.YFSB =3 ;
这是原来错误结果的语句,就是YK_YPCD.JHJG中这里还要细分,要在这里进行判断进行case,但是出来的结果只能有一个数字,用group by的话就不是所有的求和了。
已经自己弄好了:
Select sum ( YF_ZYFYMX.YPSL *(CASE
WHEN YK_TYPK.YPDW<>YK_TYPK.BFDW THEN YK_YPCD.JHJG/YK_TYPK.ZXBZ
WHEN YK_TYPK.YPDW=YK_TYPK.BFDW THEN YK_YPCD.JHJG
END ))
From YF_ZYFYMX , YK_YPCD,YK_TYPK
Where YF_ZYFYMX.YFSB =3 And YF_ZYFYMX.YPXH =YK_YPCD.YPXH
And YK_TYPK.YPXH=YF_ZYFYMX.YPXH AND YK_TYPK.YPXH=YK_YPCD.YPXH
And YF_ZYFYMX.YPCD =YK_YPCD.YPCD And YF_ZYFYMX.JFRQ > '2011-4-30 23:59:59.000'
And YF_ZYFYMX.JFRQ <= '2011-5-31 23:59:59.000' And YF_ZYFYMX.YFSB =3 ;