SQL 中的加法

Question

select 
这该写什么呢  isuse,
sum(decode(b.ign, 1, 1, 0)) naturals,
sum(decode(b.sign, 2, 2, 0)) depose,
sum(decode(b.sign, 3, 3, 0)) blank,
sum(decode(b.sign, 4, 4, 0)) other                 
from t_fsputinstore a, t_fsbillactive b现在数据库有有N条数据,其中sign分为
0 
1 naturals
2 depose
3 blank
4 blankisuse为所有大于0的状态数据的集合
sum(sign>0)的数据
或者1+2+3+4的合请问该怎么写

Answer 1

不懂你说的，不就是
select sum(b.sign) isuse, 
sum(decode(b.ign, 1, 1, 0)) naturals, 
sum(decode(b.sign, 2, 2, 0)) depose, 
sum(decode(b.sign, 3, 3, 0)) blank, 
sum(decode(b.sign, 4, 4, 0)) other                
from t_fsputinstore a, t_fsbillactive b
这样吗？

Answer 2

select 
 isuse, 
sum(decode(substr(b.sign,1,instr(b.sign,' ',1)), 1, 1, 0)) naturals, 
sum(decode(substr(b.sign,1,instr(b.sign,' ',1)),  2, 2, 0)) depose, 
sum(decode(substr(b.sign,1,instr(b.sign,' ',1)),  3, 3, 0)) blank, 
sum(decode(substr(b.sign,1,instr(b.sign,' ',1)),  4, 4, 0)) other                
from t_fsputinstore a, t_fsbillactive b 取空格前的数字来判断就OK了!

Answer 3

select 
 isuse, 
sum(decode(substr(b.sign,1,instr(b.sign,' ',1)-1), 1, 1, 0)) naturals, 
sum(decode(substr(b.sign,1,instr(b.sign,' ',1)-1),  2, 2, 0)) depose, 
sum(decode(substr(b.sign,1,instr(b.sign,' ',1)-1),  3, 3, 0)) blank, 
sum(decode(substr(b.sign,1,instr(b.sign,' ',1)-1),  4, 4, 0)) other                
from t_fsputinstore a, t_fsbillactive b 

Answer 4

不是的isuse为不是0的和sum(b.sign)就把所有的加起来了

Answer 5

不是的
isuse为不是0的和
sum(b.sign)就把所有的(0,1,2,3,4)加起来了我要排除为0的值

Answer 6

用sum(isuse)不行吗？
反正isuse取值最小为0，你取>0的和不是跟取全部的和是一样的吗？

Answer 7

我就是不明白你加0和不加0有什么区别，数字歧视嘛
那你用sum(decode(b.sign,0,null,b.sign)) isuse 吧
把取0的过滤掉