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

答案:4 悬赏:60 手机版

解决时间 2021-01-31 09:11

提问者网友：箛茗
2021-01-30 14:02

最佳答案

五星知识达人网友：过活
2021-01-30 14:27

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

全部回答

1楼网友：山河有幸埋战骨
2021-01-30 16:43

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.

2楼网友：刀戟声无边
2021-01-30 15:27

汇总用sum

3楼网友：煞尾
2021-01-30 15:20

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

我要举报

如以上回答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！

点此我要举报以上问答信息