34,576
社区成员
发帖
与我相关
我的任务
分享
-->try
declare @test table(name varchar(1),B int,C int,D int)
insert into @test
select 'a', 20, 23, 43 union all
select 'a', 28, null, 71 union all
select 'b', 36, 20, 56 union all
select 'b', 13, null, 69
;with cte as
(
select row_number() over(partition by name order by name) rn,* from @test
)
select name,B,C=isnull((select D from cte where t.rn>rn and t.name=name),C),D from cte t
/*
name B C D
---- ----------- ----------- -----------
a 20 23 43
a 28 43 71
b 36 20 56
b 13 56 69
*/
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
go
create table [test]([NAME] varchar(1),[B] int,[C] int)
insert [test]
select 'a',20,23 union all
select 'a',28,43 union all
select 'b',36,20 union all
select 'b',13,56
go
;with t
as(
select px=row_number()over(partition by [NAME] order by getdate()),
* from test
)
select [NAME],[B],
isnull((select [B]+[C] from t a where a.px=b.px-1 and a.[NAME]=b.[NAME]),[C]) as [C],
[B]+[C] as [D]
from t b
/*
[NAME] [B] [C] [D]
-----------------------------------
a 20 23 43
a 28 43 71
b 36 20 56
b 13 56 69
*/