27,582
社区成员




declare @t table(id int,f1 int,f2 int,f3 int,f4 int)
insert into @t(id,f1,f2,f3,f4)
select 1,22,33,44,55
union all select 2,32,42,43,44
union all select 3,null,11,23,22
union all select 4,null,null,12,12
union all select 5,33,33,33,33
;with t as (
select *,row_number() over(order by id) as rid from @t
),t1 as (
select a.id,b.f1,c.f2
from t a
cross apply (
select f1 where f1 is not null
union all
select top 1 f1 from t where a.f1 is null and rid<a.rid and f1 is not null order by rid desc
) b
cross apply (
select f2 where f2 is not null
union all
select top 1 f2 from t where a.f2 is null and rid<a.rid and f2 is not null order by rid desc
) c
)
update t
set f1=a.f1,f2=a.f2
from t1 a
where t.id=a.id
select * from @t a
连续null,就直接向上追溯declare @t table(id int,f1 int,f2 int,f3 int,f4 int)
insert into @t(id,f1,f2,f3,f4)
select 1,22,33,44,55
union all select 2,32,42,43,44
union all select 3,null,11,23,22
union all select 4,12,null,12,12
union all select 5,33,33,33,33
;with t as (
select *,row_number() over(order by id) as rid from @t
)
update @t set
f1=(case when f1 is null then mf1 else f1 end)
,f2=(case when f2 is null then mf2 else f2 end)
from (
select b.id as s_id,a.f1 as mf1,a.f2 as mf2,a.f3 as mf3,a.f4 as mf4
from t a
left join t b on a.rid=b.rid-1
) a
where id=s_id
select * from @t
--模拟数据
declare @temp table(id varchar(20),Cloumn1 int,Cloumn2 int)
insert into @temp
select 'id1',22,33 UNION ALL
select 'id1',32,42 UNION ALL
select 'id1',null,11 UNION ALL
select 'id1',12,null UNION ALL
select 'id1',33,33
--select * from @temp
--给表加上序号,将结果存入#temp1
SELECT * into #temp1 from (select ROW_NUMBER() over (partition by id order by id) as No,* from @temp) A
--SELECT * FROM #temp1
--按序号倒序,取相同ID,分别取出两个字段上一笔不是Null值的数据
SELECT No,Cloumn1,PrevCloumn1=(select top 1 Cloumn1 from #temp1 A where A.id=B.id and A.Cloumn1 is not null and a.No<b.No ORDER BY No DESC),
Cloumn2,PrevCloumn2=(select top 1 Cloumn2 from #temp1 A where A.id=B.id and A.Cloumn2 is not null and a.No<b.No ORDER BY No DESC)
FROM #temp1 AS B
declare @t table(id int,f1 int,f2 int,f3 int,f4 int)
insert into @t(id,f1,f2,f3,f4)
select 1,22,33,44,55
union all select 2,32,42,43,44
union all select 3,null,11,23,22
union all select 4,12,null,12,12
union all select 5,33,33,33,33
;with t as (
select *,row_number() over(order by id) as rid from @t
)
update t set
f1=(case when t.f1 is null then a.f1 else t.f1 end)
,f2=(case when t.f2 is null then a.f2 else t.f2 end)
from (
select b.id,a.f1,a.f2,a.f3,a.f4
from t a
left join t b on a.rid=b.rid-1
) a
where t.id=a.id
select * from @t
s_id是别名,你仔细看指令啊