CREATE TABLE XT_WLYS2008_001 --(还有其他字段省略)
(
F_DWBH VARCHAR(30),--单位编号
F_DWMC VARCHAR(250),--单位名称
F_JE DECIMAL(16,2),--金额
F_HXJE DECIMAL(16,2),--核销金额
F_JZFX VARCHAR(5) --结转方向(贷或借)
)
SELECT F_FLBH00,F_DWBH,F_DWMC,
SUM((CASE WHEN F_JZFX='D' THEN (F_JE-F_HXJE) END)-(CASE WHEN F_JZFX='J' THEN (F_JE-F_HXJE) END)) AS '合计'
FROM XT_WLYS2008_001
WHERE F_KMBH LIKE '1122%' AND F_CODE LIKE '2101%' AND F_FLBH00 LIKE '10%' GROUP BY F_FLBH00,F_DWBH,F_DWMC主要想求出当结转方向为贷时的SUM(F_JE-F_HXJE)然后再减去结转方向为借时的SUM(F_JE-F_HXJE)值,以上SQL语句不能实现,
请高手指点.
(
F_DWBH VARCHAR(30),--单位编号
F_DWMC VARCHAR(250),--单位名称
F_JE DECIMAL(16,2),--金额
F_HXJE DECIMAL(16,2),--核销金额
F_JZFX VARCHAR(5) --结转方向(贷或借)
)
SELECT F_FLBH00,F_DWBH,F_DWMC,
SUM((CASE WHEN F_JZFX='D' THEN (F_JE-F_HXJE) END)-(CASE WHEN F_JZFX='J' THEN (F_JE-F_HXJE) END)) AS '合计'
FROM XT_WLYS2008_001
WHERE F_KMBH LIKE '1122%' AND F_CODE LIKE '2101%' AND F_FLBH00 LIKE '10%' GROUP BY F_FLBH00,F_DWBH,F_DWMC主要想求出当结转方向为贷时的SUM(F_JE-F_HXJE)然后再减去结转方向为借时的SUM(F_JE-F_HXJE)值,以上SQL语句不能实现,
请高手指点.
SUM((CASE WHEN F_JZFX='D' THEN 1.0 WHEN F_JZFX='J' THEN -1.0 END) *(F_JE-F_HXJE)) AS '合计'
FROM XT_WLYS2008_001
WHERE F_KMBH LIKE '1122%' AND F_CODE LIKE '2101%' AND F_FLBH00 LIKE '10%' GROUP BY F_FLBH00,F_DWBH,F_DWMC
SUM(CASE WHEN F_JZFX='D' THEN F_JE-F_HXJE
WHEN F_JZFX='J' THEN -F_JE-F_HXJE
else 0 END) AS '合计'
FROM XT_WLYS2008_001
WHERE F_KMBH LIKE '1122%' AND F_CODE LIKE '2101%' AND F_FLBH00 LIKE '10%' GROUP BY F_FLBH00,F_DWBH,F_DWMC
SELECT F_FLBH00,F_DWBH,F_DWMC, sum(CASE WHEN F_JZFX='D' THEN (F_JE-F_HXJE) ELSE (F_HXJE-F_JE))
FROM XT_WLYS2008_001
WHERE F_KMBH LIKE '1122%' AND F_CODE LIKE '2101%' AND F_FLBH00 LIKE '10%'
GROUP BY F_FLBH00,F_DWBH,F_DWMC
FROM XT_WLYS2008_001
WHERE F_KMBH LIKE '1122%' AND F_CODE LIKE '2101%' AND F_FLBH00 LIKE '10%'
GROUP BY F_FLBH00,F_DWBH,F_DWMC
..差个END
SUM((CASE WHEN F_JZFX='D' THEN (F_JE-F_HXJE) ELSE 0 END)-(CASE WHEN F_JZFX='J' THEN (F_JE-F_HXJE) ELSE 0 END)) AS '合计'
FROM XT_WLYS2008_001 WHERE F_KMBH LIKE '1122%' AND F_CODE LIKE '2101%' AND F_FLBH00 LIKE '10%' GROUP BY F_FLBH00,F_DWBH,F_DWMC找到问题了,差个ELSE 楼上正解
NULL与其他值运算结果都为NULL
谢谢各位
SUM(CASE WHEN F_JZFX='D' THEN isnull(F_JE-F_HXJE,0)
WHEN F_JZFX='J' THEN -isnull(F_JE-F_HXJE,0)
else 0 END) AS '合计'
FROM XT_WLYS2008_001
WHERE F_KMBH LIKE '1122%' AND F_CODE LIKE '2101%' AND F_FLBH00 LIKE '10%' GROUP BY F_FLBH00,F_DWBH,F_DWMC