with soucre as (
select 1 as id , 3 as score
union all
select 2 as id , 4 as score
union all
select 3 as id , null as score
union all
select 4 as id , 3 as score
union all
select 5 as id , null as score
union all
select 6 as id , null as score
union all
select 7 as id , 5 as score ) -- 测试数据表创建
select step_1.id,step_1.score as old_score,step_2.score as new_score
from (
select id
,score
,sum(case when score is null then 0 else 1 end)over(order by id) as rank_id
from soucre ) as step_1 -- step_1:score是否有值分组排序
join(
select id
,score
,row_number()over(order by id) as join_id
from soucre where score is not null ) as step_2 -- step_2:score有值分组排序
on step_1.rank_id = step_2.join_id