源表T
日期 IDNO
2008-12-01 1
2008-12-02 NULL
2008-12-03 NULL
2008-12-06 2
2008-12-08 3
2008-12-09 4
2008-12-09 NULL
2008-12-10 NULL想得到目的表D
日期 IDNO
2008-12-01 1
2008-12-02 1
2008-12-03 1
2008-12-06 2
2008-12-08 3
2008-12-09 4
2008-12-09 4
2008-12-10 4也就是IDNO列如果是NULL用前面的IDNO值替代
日期 IDNO
2008-12-01 1
2008-12-02 NULL
2008-12-03 NULL
2008-12-06 2
2008-12-08 3
2008-12-09 4
2008-12-09 NULL
2008-12-10 NULL想得到目的表D
日期 IDNO
2008-12-01 1
2008-12-02 1
2008-12-03 1
2008-12-06 2
2008-12-08 3
2008-12-09 4
2008-12-09 4
2008-12-10 4也就是IDNO列如果是NULL用前面的IDNO值替代
set nocount on
create table test(日期 varchar(20),IDNO varchar(20))
insert into test select '2008-12-01','1'
insert into test select '2008-12-02',Null
insert into test select '2008-12-03',Null
insert into test select '2008-12-06','2'
insert into test select '2008-12-08','3'
insert into test select '2008-12-09','4'
insert into test select '2008-12-09',Null
insert into test select '2008-12-10',Null
go
--测试
select 日期,isnull(IDNO,
(select top 1 IDNO from test where 日期<a.日期 and IDNO is not null
order by 日期 desc
)) from test a/*
2008-12-01 1
2008-12-02 1
2008-12-03 1
2008-12-06 2
2008-12-08 3
2008-12-09 4
2008-12-09 3
2008-12-10 4
*/--删除测试环境
drop table test
set nocount off
INSERT @t SELECT '2008-12-01',1
UNION ALL SELECT '2008-12-02',NULL
UNION ALL SELECT '2008-12-03',NULL
UNION ALL SELECT '2008-12-06',2
UNION ALL SELECT '2008-12-08',3
UNION ALL SELECT '2008-12-09',4
UNION ALL SELECT '2008-12-09',NULL
UNION ALL SELECT '2008-12-10',NULL
/************/
/*Test Data*/
/***fcuandy**/
/*2008-12-23*/
/************/
SELECT * FROM @tDECLARE @IDNO INT
UPDATE @t SET @idno = ISNULL(IDNO,@idno),IDNO=@idno
SELECT * FROM @t
/*
2008-12-01 00:00:00.000 1
2008-12-02 00:00:00.000 1
2008-12-03 00:00:00.000 1
2008-12-06 00:00:00.000 2
2008-12-08 00:00:00.000 3
2008-12-09 00:00:00.000 4
2008-12-09 00:00:00.000 4
2008-12-10 00:00:00.000 4
*/
UPDATE @t SET @idno = ISNULL(IDNO,@idno),IDNO=@idno