SQL 如何将正负数分成各自的列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1698805/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to separate positive and negative numbers into their own columns?
提问by Mike Sickler
I have a table with the following columns and data:
我有一个包含以下列和数据的表：
activity_dt | activity_amt
2009-01-01 | -500
2009-01-01 | 750
Can I write a query that looks at the sign of activity_amtand puts it in the credits column if it's positive, and the debits column if it's negative? (I'm using Sybase)
我可以编写一个查询，查看 的符号activity_amt并将其放在贷方列（如果它是正的）和借方列（如果它是负的）？（我正在使用 Sybase）
activity_dt | debits | credits
2009-01-01 | -500 | 750
回答by schinazi
select activity_dt,
sum(case when activity_amt < 0 then activity_amt else 0 end) as debits,
sum(case when activity_amt > 0 then activity_amt else 0 end) as credits
from the_table
group by activity_dt
order by activity_dt
回答by Guffa
I'm not sure about the exact syntax in Sybase, but you should be able to group on the date and sum up the positive and negative values:
我不确定 Sybase 中的确切语法，但您应该能够对日期进行分组并总结正值和负值：
select
activity_dt,
sum(case when activity_amt < 0 then activity_amt else 0 end) as debits,
sum(case when activity_amt >= 0 then activity_amt else 0 end) as credits
from
theTable
group by
activity_dt
回答by TinTin
select (select JV_GroupsHead.GroupTitle
from JV_GroupsHead
whereJV_GroupsHead.Id=jv.GroupId) as 'GroupName'
,jv.Revenue
,jv.AccountNo
,jv.AccountNoTitle
,(case when jv.Revenue < 0 then jv.Revenue else 0 end) as 'debits'
,(case when jv.Revenue> 0 then jv.Revenue else 0 end) as 'credits'
from JVFunction1('2010-07-08','2010-08-08') as jv
回答by Mihir Kale
I found a new answer to this problem using the DECODEfunction.
I hope this turns out to be useful for everyone.
我使用该DECODE函数找到了这个问题的新答案。我希望这对每个人都有用。
select activity_dt,
sum((DECODE(activity_amt /-ABS(activity_amt), 1, activity_amt, 0))) as credits,
sum((DECODE(activity_amt /-ABS(activity_amt), -1, activity_amt, 0))) as debits
from the_table
group by activity_dt
order by activity_dt;
