求1SQL,在线_Sql Server

求一SQL,急在线！
字段1，字段2，字段3
A001, 100, Yes
null, null, null
null, null, null
null, null, null
B001, NULL, No
null, null, null
null, null, null
null, null, null
C001, 90 , Yes

改为
字段1，字段2，字段3
A001, 100, Yes
A001, 100, Yes
A001, 100, Yes
A001, 100, Yes
B001, NULL, No
B001, NULL, No
B001, NULL, No
C001, 90 , Yes

将是改NULL补充了它！

------解决方案--------------------

SQL code

--> 测试数据：[tb]IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GO CREATE TABLE [tb]([字段1] VARCHAR(4),[字段2] INT,[字段3] VARCHAR(3))INSERT [tb]SELECT 'A001',100,'Yes' UNION ALLSELECT NULL,NULL,NULL UNION ALLSELECT NULL,NULL,NULL UNION ALLSELECT NULL,NULL,NULL UNION ALLSELECT 'B001',NULL,'No' UNION ALLSELECT NULL,NULL,NULL UNION ALLSELECT NULL,NULL,NULL UNION ALLSELECT NULL,NULL,NULL UNION ALLSELECT 'C001',90,'Yes'--------------开始查询--------------------------;WITH t AS(SELECT *,row=ROW_NUMBER() OVER(ORDER BY GETDATE()) FROM [tb])SELECT CASE WHEN [字段1] IS NULL THEN (select TOP 1 [字段1] FROM t WHERE row<=a.row AND [字段1] IS NOT NULL ORDER BY row DESC) ELSE [字段1]END, CASE WHEN [字段2] IS NULL THEN (select TOP 1 [字段2] FROM t WHERE row<=a.row AND [字段1] IS NOT NULL ORDER BY row DESC) ELSE [字段2]END, CASE WHEN [字段3] IS NULL THEN (select TOP 1 [字段3] FROM t WHERE row<=a.row AND [字段1] IS NOT NULL ORDER BY row DESC) ELSE [字段3]END FROM t AS a----------------结果----------------------------/*                  ---- ----------- ----A001 100         YesA001 100         YesA001 100         YesA001 100         YesB001 NULL        NoB001 NULL        NoB001 NULL        NoB001 NULL        NoC001 90          Yes(9 行受影响)*/
------解决方案--------------------
SQL codecreate table #tt (id int identity(1,1) ,col1 varchar(10) null ,col2 smallint null,col3 varchar(10) null)insert into #tt(col1,col2,col3)select 'A001', 100, 'Yes'union all select null, null, nullunion all select null, null, nullunion all select null, null, nullunion all select 'B001', NULL, 'No'union all select null, null, nullunion all select null, null, nullunion all select null, null, nullunion all select 'C001', 90 , 'Yes';update Aset A.col1=T.col1,A.col2=T.col2,A.col3=T.col3from #tt as A cross apply(select top 1 B.col1,B.col2,B.col3from #tt as Bwhere B.id<A.id and B.col1 is not nullorder by B.id desc) Twhere A.col1 is  null;select * from #tt order by id;/*id          col1       col2   col3----------- ---------- ------ ----------1           A001       100    Yes2           A001       100    Yes3           A001       100    Yes4           A001       100    Yes5           B001       NULL   No6           B001       NULL   No7           B001       NULL   No8           B001       NULL   No9           C001       90     Yes(9 row(s) affected)*/drop table #tt;
------解决方案--------------------
SQL codecreate table #tt (id int identity(1,1) ,col1 varchar(10) null ,col2 smallint null,col3 varchar(10) null)insert into #tt(col1,col2,col3)select 'A001', 100, 'Yes'union all select null, null, nullunion all select null, null, nullunion all select null, null, nullunion all select 'B001', NULL, 'No'union all select null, null, nullunion all select null, null, nullunion all select null, null, nullunion all select 'C001', 90 , 'Yes';update Aset A.col1=T.col1,A.col2=T.col2,A.col3=T.col3from #tt as A cross apply(select top 1 B.col1,B.col2,B.col3from #tt as Bwhere B.id<A.id and B.col1 is not nullorder by B.id desc) Twhere A.col1 is  null;select * from #tt order by id;/*id          col1       col2   col3----------- ---------- ------ ----------1           A001       100    Yes2           A001       100    Yes3           A001       100    Yes4           A001       100    Yes5           B001       NULL   No6           B001       NULL   No7           B001       NULL   No8           B001       NULL   No9           C001       90     Yes(9 row(s) affected)*/drop table #tt;