请教sql语句，修改指定条件单元格的值为上一行同列单元格的值

[InkyShark]

现有一需求，数据中有为空的值，
想通过语句，将此空值修改成上一条同列的值。id为guid

id 列1 列2 列3 列4
xx 22 33 44 55
xx 32 42 43 44
xx 11 23 22
xx 12 12 12
xx 33 33 33 33


现在想通过语句将 第三行列1空值改成 第二行列1的“32”，第四行列2的空值改成第三行列2的“11”
以此类推。

[文盲老顾]

自行修改 row_number 里的order就可以

[InkyShark]

引用 3 楼 文盲老顾 的回复:

自行修改 row_number 里的order就可以

where条件是ID=sid 我表id是guid。。。

[文盲老顾]

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是别名，你仔细看指令啊

[小野马1209]

--模拟数据
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 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

[InkyShark]

引用 1 楼 文盲老顾 的回复:

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

数据库中ID列是 guid() 非int自增id 可以根据time排序

[xiaodai511]

引用 1 楼 文盲老顾 的回复:

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

你好 老师，同样遇到了这个问题，如果上一条同样是null呢或者同列连续几个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,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，就直接向上追溯