declare @ta table(name varchar(2),num int) insert @ta select 'A', 20 insert @ta select 'A', 10 insert @ta select 'B', 30 insert @ta select 'B', 20 insert @ta select 'B', -10select *,id=identity(int,1,1) into # from @taselect name,[num]=case when num>con then con else num end from (select a.id,a.name,a.num,con=sum(b.num) from # a join # b on a.name=b.name where b.id!<a.id group by a.id,a.name,a.num)ta where num>0 name num ---- ----------- A 20 A 10 B 30 B 10(所影响的行数为 4 行)
insert @ta select 'A', 20
insert @ta select 'A', 10
insert @ta select 'B', 30
insert @ta select 'B', 20
insert @ta select 'B', -10select *,id=identity(int,1,1) into # from @taselect name,[num]=case when num>con then con else num end
from
(select a.id,a.name,a.num,con=sum(b.num) from # a join # b on a.name=b.name
where b.id!<a.id
group by a.id,a.name,a.num)ta
where num>0
name num
---- -----------
A 20
A 10
B 30
B 10(所影响的行数为 4 行)