有这么个表
sp_id num
1001 2
1001 3
1001 -1
1001 -3
1002 10
1002 2
1002 -5
怎样实现这样的查询结果?
sp_id num num
1001 5 -4
1002 12 -5
也就是把同一个sp_id号的num正数相加,负数相加进行统计.
sql语句该如何写呢?
sp_id num
1001 2
1001 3
1001 -1
1001 -3
1002 10
1002 2
1002 -5
怎样实现这样的查询结果?
sp_id num num
1001 5 -4
1002 12 -5
也就是把同一个sp_id号的num正数相加,负数相加进行统计.
sql语句该如何写呢?
(select sp_id,sum(num) as num from table
where num>0 group by sp_id) as a,
(select sp_id,sum(num) as num from table
where num<0 group by sp_id) as b
where a.sp_id=b.sp_id
(select sum(num) from table where sp_id = a.sp_id and num > 0) as num,
(select sum(num) from table where sp_id = a.sp_id and num < 0) as num1,
from table as a
(Select Sum(num) from 表名 i where i.sp_id = x.sp_id and num >= 0) as PosiNum,
(Select Sum(num) from 表名 j where j.sp_id = x.sp_id and num < 0) as NegiNum
from 表名 x
group by sp_id
order by sp_id