34,575
社区成员
发帖
与我相关
我的任务
分享
-->SQL查询如下:
SELECT
SUM(
CASE ftype
WHEN 3 THEN - Famount
WHEN 5 THEN Famount
ELSE 0
END
) Famount
FROM tb
WHERE [fcust] = 111
/*
Famount
-----------
200
(1 行受影响)
*/
--> 生成测试数据表: [tb]
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
fcust,
sum(case ftype when 3 then -Famount else famount end) as total
from
tb
group by
fcust
select fcust,sum(case ftype when 3 then - Famount else Famount end) Famount
from tb
where ftype in(3,5)
group by fcust