FID FType Famount fcust
1 3 100 111
2 5 200 111
3 3 400 111
4 5 500 111例如这个表,我需要查询出来一个结果值,就是111这个客户,当Ftype=3时,减去相应的Famount值,当Ftype =5 时,加上相应的Famount值,最后结果得到一个Sum值,怎么写呢?用CASE么
1 3 100 111
2 5 200 111
3 3 400 111
4 5 500 111例如这个表,我需要查询出来一个结果值,就是111这个客户,当Ftype=3时,减去相应的Famount值,当Ftype =5 时,加上相应的Famount值,最后结果得到一个Sum值,怎么写呢?用CASE么
from tb
where ftype in(3,5)
group by fcust
fcust,
sum(case ftype when 3 then -Famount else famount end) as total
from
tb
group by
fcust
from tb where fcust='111'
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([FID] [int],[FType] [int],[Famount] [int],[fcust] [int])
INSERT INTO [tb]
SELECT '1','3','100','111' UNION ALL
SELECT '2','5','200','111' UNION ALL
SELECT '3','3','400','111' UNION ALL
SELECT '4','5','500','111'--SELECT * FROM [tb]-->SQL查询如下:
SELECT fcust,
SUM(
CASE ftype
WHEN 3 THEN - Famount
WHEN 5 THEN Famount
ELSE 0
END
) Famount
FROM tb
WHERE [fcust] = 111
GROUP BY fcust
/*
fcust Famount
----------- -----------
111 200(1 行受影响)
*/
SELECT
SUM(
CASE ftype
WHEN 3 THEN - Famount
WHEN 5 THEN Famount
ELSE 0
END
) Famount
FROM tb
WHERE [fcust] = 111/*
Famount
-----------
200(1 行受影响)
*/