SQL抽出某字段的正数和负数结果，并进行汇总查询

SQL抽出某字段的正数和负数结果，并进行汇总查询

Select u_id,sum(case when field > 0 then field else 0 end) as 正数,
sum(case when field < 0 then field else 0 end) as 负数,
sum(case when field > 0 then field else 0 end) - sum(case when field < 0 then field else 0 end) as 实际
group by u_id
order by u_id

select sum(case when filed > 0 then field else 0 end) as zhengshu,
sum(case when filed < 0 then field else 0 end) as fushu
from tab.

汇总用sum

IF OBJECT_ID('Tempdb..#1') IS NOT NULL
DROp TABLE #1
CREATE TABLE #1(U_ID INT,记账数据 int)
INSERT #1 SELECT 1,200
INSERT #1 SELECT 2,300
INSERT #1 SELECT 3,400
INSERT #1 SELECT 2,-50
INSERT #1 SELECT 5,600
INSERT #1 SELECT 3,-70
GO
SELECT
U_ID,
sum(CASE WHEN SIGN(记账数据)=1 THEN 记账数据 ELSE 0 END) AS '支出',
sum(CASE WHEN SIGN(记账数据)=-1 THEN 记账数据 ELSE 0 END) AS '收入',
sum(记账数据) AS '实际'
FROM #1
GROUP BY U_ID