与 SUM() 窗口功能：

select id, foreignkey, value1 + coalesce(total, 0) value1, value2
from (
  select *, 
    sum(case when value2 = 0 then value1 end) over (partition by foreignkey) total
  from tablename
) t 
where value2 <> 0

请看演示。
结果：

> id | foreignkey | value1 | value2
> -: | ---------: | -----: | -----:
>  2 |        500 |    800 |     15
>  3 |        500 |    470 |     25
>  4 |        999 |    250 |     57

嗯。假设这不会过滤掉所有行，您可以使用如下窗口函数：

select id, foreignkey, value1, value2 + (case when seqnum = 1 then value2_0 else 0 end)
from (select t.*,
             row_number() over (partition by foreignkey order by value1 desc) as seqnum,
             sum(case when value1 = 0 then value2 end) over (partition by foreignkey) as value2_0
      from t
     ) t
where value2 <> 0;

一种方法是将所有零行视为一个组，将所有其他行视为另一个组（基于foreignkey），然后简单地联接并添加值，最后只选择所需的值：

;with cte as
(
    select id, foreignkey, value1, value2,dense_rank() over (partition by foreignkey order by (case when value2 = 0 then 0 else 1 end)) as rn
    from @t t1
)
,cte2 as
(
    select t1.id, t1.foreignkey, t1.value1 + isnull(t2.value1,0) as value1, t1.value2
    from cte t1
    left join cte t2 on (t2.foreignkey = t1.foreignkey and t1.rn<> t2.rn)
)
select * from cte2
where value2 <> 0

请在这里找到小提琴。