表:tblA ,
字段:name ,num
a 1
a -2
b -1
b -3
c 1
c 4
想通过name对表进行分组求和,num中有负数(或全为负数)的求和,全为正数的保持不变。
返回结果如:
name num
a -1
b -4
c 1
c 4
如何实现?
字段:name ,num
a 1
a -2
b -1
b -3
c 1
c 4
想通过name对表进行分组求和,num中有负数(或全为负数)的求和,全为正数的保持不变。
返回结果如:
name num
a -1
b -4
c 1
c 4
如何实现?
“全为正数的保持不变”,即name 为C的那两行不要分组,保持不变
select name,sum(num) as num from tb where num<0 group by name
union all
select name,num from tb where num>0
union all
select name ,num from tb where num > 0 and name not in (select distinct name from tb where num < 0)
(
name varchar(10),
num int
)
insert into tb values('a',1)
insert into tb values('a',-2)
insert into tb values('b',-1)
insert into tb values('b',-3)
insert into tb values('c',1)
insert into tb values('c',4)
select name ,sum(num)num from tb where name in (select distinct name from tb where num < 0 ) group by name
union all
select name ,num from tb where name not in (select distinct name from tb where num < 0) and num > 0
/*
name,num
a,-1
b,-4
c,1
c,4(4 行受影响)
“num中有负数的求和”,即num中有负数,也有正数时求和,如例中name为a的行求和