意思:我实发金额中包括了班内工资+班外工资,当chk班内=0(不在班内),实际班内工资=实发金额-数量*单价(不在班内的工资)

我有一个表(Tb_工资姓名_b)如下:
姓名    数量     单价    chk班内     班内金额     实发金额   jlsjh
xxa     52        1        0            50          102      _49P18Y2VP_49P18Y2VQ
xxb     48        1        1            50           98      _49P18Y2VP_49P18Y2VQ                                         
xxc     0         0        1            50           50      _49P18Y2VP_49P18Y2VQ
用这个sql语句可以得到正确答案---198:
SELECT SUM(实发金额) -(SELECT SUM(数量 * 单价) FROM  Tb_工资姓名_b WHERE jlsjh_tb_工资姓名 = '_49P18Y2VP_49P18Y2VQ' AND chk班内 = 0) AS 实发金额
FROM Tb_工资姓名_b WHERE jlsjh_tb_工资姓名 = '_49P18Y2VP_49P18Y2VQ'
但我想用一种更简便的case when如下语句却得不到正确答案250:
SELECT  SUM(实发金额 -CASE WHEN chk班内 = 0 THEN 0 ELSE 数量 * 单价 END) AS 实发金额 FROM Tb_工资姓名_b WHERE jlsjh_tb_工资姓名 = '_49P18Y2VP_49P18Y2VQ'
请教一下各位,怎么写?
再简化:
SELECT  SUM(CASE WHEN chk班内 = 0 THEN 0 ELSE 数量 * 单价 END) AS 实发金额 FROM Tb_工资姓名_b WHERE jlsjh_tb_工资姓名 = '_49P18Y2VP_49P18Y2VQ'
不起作用?为何?



[ 本帖最后由 lgp740401 于 2015-4-16 12:48 编辑 ]

难道用case when用不上吗?


[ 本帖最后由 lgp740401 于 2015-4-16 12:57 编辑 ]

SELECT  SUM(班内金额+CASE WHEN chk班内 = 0 THEN 0 ELSE 数量 * 单价 END) AS 实发金额 FROM Tb_工资姓名_b WHERE jlsjh = '_49P18Y2VP_49P18Y2VQ'
这样就对了

SELECT  SUM(实发金额 -CASE WHEN chk班内 = 1 THEN 0 ELSE 数量 * 单价 END) AS 实发金额 FROM Tb_工资姓名_b WHERE jlsjh = '_49P18Y2VP_49P18Y2VQ'
这样也是一样的