22,209
社区成员
发帖
与我相关
我的任务
分享
declare @t table(rid int identity(1,1),id int,adddate datetime)
insert @t(id,adddate) select top 20 number,'2010-1-1 10:10:01' from master..spt_values where type = 'P' and number > 0 order by newid()
select * from @t --rid仅是配合显示,我生成5个一组的,你50的只要改一下下面注释的地方
/*
rid id adddate
----------- ----------- ------------------------------------------------------
1 234 2010-01-01 10:10:01.000
2 84 2010-01-01 10:10:01.000
3 44 2010-01-01 10:10:01.000
4 175 2010-01-01 10:10:01.000
5 199 2010-01-01 10:10:01.000
6 37 2010-01-01 10:10:01.000
7 104 2010-01-01 10:10:01.000
8 47 2010-01-01 10:10:01.000
9 56 2010-01-01 10:10:01.000
10 108 2010-01-01 10:10:01.000
11 213 2010-01-01 10:10:01.000
12 166 2010-01-01 10:10:01.000
13 254 2010-01-01 10:10:01.000
14 92 2010-01-01 10:10:01.000
15 67 2010-01-01 10:10:01.000
16 80 2010-01-01 10:10:01.000
17 161 2010-01-01 10:10:01.000
18 152 2010-01-01 10:10:01.000
19 17 2010-01-01 10:10:01.000
20 170 2010-01-01 10:10:01.000
(所影响的行数为 20 行)
*/
declare @i int ,@s datetime
set @i = 0
set @s = '2010-1-1 10:10:01'
update @t
set addDate = dateadd(d,(@I-1)/5,@s),@i = @I +1 --你把这儿5改成50
select * from @t
/*
rid id adddate
----------- ----------- ------------------------------------------------------
1 82 2010-01-01 10:10:01.000
2 19 2010-01-01 10:10:01.000
3 246 2010-01-01 10:10:01.000
4 20 2010-01-01 10:10:01.000
5 38 2010-01-01 10:10:01.000
6 107 2010-01-02 10:10:01.000
7 214 2010-01-02 10:10:01.000
8 192 2010-01-02 10:10:01.000
9 36 2010-01-02 10:10:01.000
10 77 2010-01-02 10:10:01.000
11 236 2010-01-03 10:10:01.000
12 254 2010-01-03 10:10:01.000
13 65 2010-01-03 10:10:01.000
14 164 2010-01-03 10:10:01.000
15 196 2010-01-03 10:10:01.000
16 41 2010-01-04 10:10:01.000
17 48 2010-01-04 10:10:01.000
18 81 2010-01-04 10:10:01.000
19 51 2010-01-04 10:10:01.000
20 250 2010-01-04 10:10:01.000
(所影响的行数为 20 行)
*/
alter table tb add date datetime
;with t as
(
select *,row=(select count(*)+1 from tb where id <a.id) from tb
)
update t set date=datediff(dd,'2010-1-1 10:10:01',row/50)
declare @i int ,@s datetime
set @i = 0
set @s = '2010-1-1 10:10:01'
update ta
set addDate = dateadd(d,(@I-1)/50,@s),@i = @I +1