with c as(

select *,

ROW_NUMBER()over(order by a asc) as xh

from (

select 1 a,2 as b

union all

select 2 a,null

union all

select 3 a,3

union all

select 4 a,null

union all

select 5 a,null

union all

select 6 a,7

) a

)

select isnull(b,(select top 1 b from c where not b is null and xh<a.xh order by xh desc )) as 新值,* from c as a

用sql处理很麻烦的，还不如在页面处理；下面是页面处理的例子

with a as (

select '71' [id],20 [sl]

union all

select '72' [id],null [sl]

union all

select '73' [id],50 [sl]

union all

select '74' [id],null [sl]

union all

select '75' [id],10 [sl]

union all

select '76' [id],null [sl]

union all

select '77' [id],null [sl]

union all

select '78' [id],70 [sl]

)

SELECT *,(CASE WHEN SL IS NULL THEN (SELECT TOP 1 SL FROM A T WHERE T.ID<A.ID AND T.sl IS NOT NULL ORDER BY T.id DESC) ELSE SL END) FROM A

PS：还是送点分嘛

with temp1 as (

select 70 as a,20 as b 

union all 

select 71 as a,null as b 

union all 

select 72 as a,null as b 

union all 

select 73 as a,21 as b 

union all 

select 74 as a,null as b 

union all 

select 75 as a,null as b

union all 

select 76 as a,32 as b 

union all 

select 77 as a,35 as b 

 )

 select a.*,coalesce (a.b,b.b)

 from temp1 a 

 left join (

 select * 

 from 

 (

  select a,b,lead (a)over() as lead_rn

   from temp1

  where b is not null 

 )s 

 where lead_rn is not null 

 ) b 

 on a.a >= b.a and a.a< b.lead_rn