数据表如下:
Column1 Column2 Coulumn3
A 22 33
NULL 33 3
NULL 44 33
NULL 2 33
NULL 5 33
B 122 33
NULL 133 3
NULL 244 33
NULL 3 33
NULL 22 33
要求得到表数据如下:
Column1 Column2 Coulumn3
A 22 33
A 33 3
A 44 33
A 2 33
A 5 33
B 122 33
B 133 3
B 244 33
B 3 33
B 22 33也就是根据不通的组,补齐相关组的数据,比如组:A和B一个查询SQL能搞定吗?
Column1 Column2 Coulumn3
A 22 33
NULL 33 3
NULL 44 33
NULL 2 33
NULL 5 33
B 122 33
NULL 133 3
NULL 244 33
NULL 3 33
NULL 22 33
要求得到表数据如下:
Column1 Column2 Coulumn3
A 22 33
A 33 3
A 44 33
A 2 33
A 5 33
B 122 33
B 133 3
B 244 33
B 3 33
B 22 33也就是根据不通的组,补齐相关组的数据,比如组:A和B一个查询SQL能搞定吗?
update tb set Column1=@str1,@str1=(case when Column1 is not null then Column1 end)
declare @table table (Column1 varchar(1),Column2 int,Coulumn3 int)
insert into @table
select 'A',22,33 union all
select null,33,3 union all
select null,44,33 union all
select null,2,33 union all
select null,5,33 union all
select 'B',122,33 union all
select null,133,3 union all
select null,244,33 union all
select null,3,33 union all
select null,22,33declare @t varchar(4)
update @table
set @t=isnull(Column1,@t),Column1=isnull(@t,Column1)select * from @table
/*
Column1 Column2 Coulumn3
------- ----------- -----------
A 22 33
A 33 3
A 44 33
A 2 33
A 5 33
B 122 33
B 133 3
B 244 33
B 3 33
B 22 33
*/
declare @table table (Column1 varchar(1),Column2 int,Coulumn3 int)
insert into @table
select 'A',22,33 union all
select null,33,3 union all
select null,44,33 union all
select null,2,33 union all
select null,5,33 union all
select 'B',122,33 union all
select null,133,3 union all
select null,244,33 union all
select null,3,33 union all
select null,22,33declare @t varchar(4)
--这种没有变化
--update @table set Column1=@t,@t=(case when Column1 is not null then Column1 end)
--这种也没有变化
--update @table set @t=Column1,Column1=isnull(@t,Column1)--这种是AAAABBBB的
--update @table set @t=isnull(Column1,@t),Column1=isnull(@t,Column1)
--这种也是AAAABBBB的
update @table set @t=isnull(Column1,@t),Column1=@t--这种就全null了
--update @table set Column1=isnull(nullif(@t,Column1),@t),@t=Column1
--这种就全A了
--update @table set Column1=@t,@t=isnull(@t,Column1)select * from @table
/*
Column1 Column2 Coulumn3
------- ----------- -----------
A 22 33
A 33 3
A 44 33
A 2 33
A 5 33
B 122 33
B 133 3
B 244 33
B 3 33
B 22 33
*/
;with cte as
(
select *,rn=ROW_NUMBER() over(order by GETDATE()) from @table
)
--select * from cteselect (select top 1 b.column1 from cte b where b.rn<=a.rn and b.column1 is not null order by rn desc),* from cte a
DROP TABLE #temp
go
CREATE TABLE #temp (Column1 varchar(1),Column2 int,Coulumn3 int)
insert into #temp
select 'A',22,33 union all
select null,33,3 union all
select null,44,33 union all
select null,2,33 union all
select null,5,33 union all
select 'B',122,33 union all
select null,133,3 union all
select null,244,33 union all
select null,3,33 union all
select null,22,33
GO
--SQL:
;WITH cte AS
(SELECT rowno = ROW_NUMBER() OVER(ORDER BY GETDATE()), * FROM #temp)
UPDATE T
SET Column1 = (SELECT TOP(1) Column1 FROM cte WHERE rowno < T.rowno AND Column1 IS NOT NULL ORDER BY Column1 DESC)
FROM cte T
WHERE Column1 IS NULL
GO
--RESULT
SELECT * FROM #temp
/*
A 22 33
A 33 3
A 44 33
A 2 33
A 5 33
B 122 33
B 133 3
B 244 33
B 3 33
B 22 33
*/