我现在想得到指定行的下一行,请问怎么得到啊
ID DATE
442 2011-07-08 11:13:49
443 2011-07-09 11:13:49
750
751
780我现在的目的就是想日期为空的递增实现这种效果,说明下,ID 是不连续的。前面已有日期我是通过游标加进去的,到750就不加了。
ID DATE
442 2011-07-08 11:13:49
443 2011-07-09 11:13:49
750 2011-07-10 11:13:49
751 2011-07-11 11:13:49
780 2011-07-12 11:13:49
ID DATE
442 2011-07-08 11:13:49
443 2011-07-09 11:13:49
750
751
780我现在的目的就是想日期为空的递增实现这种效果,说明下,ID 是不连续的。前面已有日期我是通过游标加进去的,到750就不加了。
ID DATE
442 2011-07-08 11:13:49
443 2011-07-09 11:13:49
750 2011-07-10 11:13:49
751 2011-07-11 11:13:49
780 2011-07-12 11:13:49
select min(id) from [Table] where date is null
select min(id) from [Table] where id=443--指定443
declare @date datetimeupdate tb
set @date = dateadd(day,1,[date]),[date] = @date???
insert into tb values(442 ,'2011-07-08 11:13:49')
insert into tb values(443 ,'2011-07-09 11:13:49')
insert into tb values(750,null)
insert into tb values(751,null)
insert into tb values(780,null)
goselect id , DATE = case when date is not null then date else (select max(date) from tb) end from tb tdrop table tb/*
id DATE
----------- ------------------------------------------------------
442 2011-07-08 11:13:49.000
443 2011-07-09 11:13:49.000
750 2011-07-09 11:13:49.000
751 2011-07-09 11:13:49.000
780 2011-07-09 11:13:49.000(所影响的行数为 5 行)
*/
insert into T (id) select 442
insert into T (id) select 443
insert into T (id) select 750
insert into T (id) select 751
insert into T (id) select 780
Go
declare @min int
select @min=min(id) from T
Update T
set [date]= dateadd(day, @min+isnull((select count(*) from T as A where A.id<=T.id),0) , getdate() )
GOselect * from T
/*
442 2011-07-08 12:05:34.000
443 2011-07-09 12:05:34.000
750 2011-07-10 12:05:34.000
751 2011-07-11 12:05:34.000
780 2011-07-12 12:05:34.000*/
GO
Drop table T
insert into tb values(442 ,'2011-07-08 11:13:49')
insert into tb values(443 ,'2011-07-09 11:13:49')
insert into tb values(750,null)
insert into tb values(751,null)
insert into tb values(780,null)
go--1
select id , DATE = case when date is not null then date else (select max(date) from tb) end from tb t--2
select id , DATE = case when date is not null then date else (select top 1 date from tb where date is not null order by id desc) end from tb tdrop table tb/*
id DATE
----------- ------------------------------------------------------
442 2011-07-08 11:13:49.000
443 2011-07-09 11:13:49.000
750 2011-07-09 11:13:49.000
751 2011-07-09 11:13:49.000
780 2011-07-09 11:13:49.000(所影响的行数为 5 行)
*/