如何给一个表某列加上指定的随机数
一、原始数据
create table #test (name varchar(10),ddate datetime,date1 datetime,date2 datetime)
insert into #test
select '张三','2013-09-01','2013-09-01 09:00:00.000','2013-09-01 17:00:00.000' union all
select '张三','2013-09-02','2013-09-02 09:00:00.000','2013-09-02 17:00:00.000' union all
select '张三','2013-09-03','2013-09-03 09:00:00.000','2013-09-03 17:00:00.000' union all
select '张三','2013-09-04','2013-09-04 09:00:00.000','2013-09-04 17:00:00.000' union all
select '张三','2013-09-05','2013-09-05 09:00:00.000','2013-09-05 17:00:00.000' union all
select '张三','2013-09-06','2013-09-06 09:00:00.000','2013-09-06 17:00:00.000'
select * from #test
create table #test (name varchar(10),ddate datetime,date1 datetime,date2 datetime) insert into #test select '张三','2013-09-01','2013-09-01 09:00:00.000','2013-09-01 17:00:00.000' union allselect '张三','2013-09-02','2013-09-02 09:00:00.000','2013-09-02 17:00:00.000' union allselect '张三','2013-09-03','2013-09-03 09:00:00.000','2013-09-03 17:00:00.000' union allselect '张三','2013-09-04','2013-09-04 09:00:00.000','2013-09-04 17:00:00.000' union allselect '张三','2013-09-05','2013-09-05 09:00:00.000','2013-09-05 17:00:00.000' union allselect '张三','2013-09-06','2013-09-06 09:00:00.000','2013-09-06 17:00:00.000' --sql: select name,ddate, date1 = DATEADD(MINUTE, ABS(CHECKSUM(NEWID()))%10 + 1, date1), date2 = DATEADD(MINUTE, ABS(CHECKSUM(NEWID()))%11 + 5, date2) from #test /* name ddate date1 date2 张三 2013-09-01 00:00:00.000 2013-09-01 09:08:00.000 2013-09-01 17:10:00.000 张三 2013-09-02 00:00:00.000 2013-09-02 09:10:00.000 2013-09-02 17:11:00.000 张三 2013-09-03 00:00:00.000 2013-09-03 09:08:00.000 2013-09-03 17:06:00.000 张三 2013-09-04 00:00:00.000 2013-09-04 09:08:00.000 2013-09-04 17:06:00.000 张三 2013-09-05 00:00:00.000 2013-09-05 09:03:00.000 2013-09-05 17:05:00.000 张三 2013-09-06 00:00:00.000 2013-09-06 09:09:00.000 2013-09-06 17:13:00.000 */
create
table
#test(
name
varchar
(10),ddate datetime,date1 datetime,date2 datetime)
insert
into
#test
select
'张三'
,
'2013-09-01'
,
'2013-09-01 09:00:00.000'
,
'2013-09-01 17:00:00.000'
union
all
select
'张三'
,
'2013-09-02'
,
'2013-09-02 09:00:00.000'
,
'2013-09-02 17:00:00.000'
union
all
select
'张三'
,
'2013-09-03'
,
'2013-09-03 09:00:00.000'
,
'2013-09-03 17:00:00.000'
union
all
select
'张三'
,
'2013-09-04'
,
'2013-09-04 09:00:00.000'
,
'2013-09-04 17:00:00.000'
union
all
select
'张三'
,
'2013-09-05'
,
'2013-09-05 09:00:00.000'
,
'2013-09-05 17:00:00.000'
union
all
select
'张三'
,
'2013-09-06'
,
'2013-09-06 09:00:00.000'
,
'2013-09-06 17:00:00.000'
-- 更新
declare
ap
scroll
cursor
for
select
name
,ddate,date1,date2
from
#test
declare
@n
varchar
(10),@d datetime,@d1 datetime,@d2 datetime
open
ap
fetch
first
from
ap
into
@n,@d,@d1,@d2
while(@@fetch_status<>-1)
begin
update
#test
set
date1=dateadd(mi,
cast
(rand()*10
as
int
),date1),
date2=dateadd(mi,5+
cast
(rand()*10
as
int
),date2)
where
name
=@n
and
ddate=@d
fetch
next
from
ap
into
@n,@d,@d1,@d2
end
close
ap
deallocate
ap
-- 结果
select
*
from
#test
/*
name
ddate date1 date2
---------- ----------------------- ----------------------- -----------------------
张三 2013-09-01 00:00:00.000 2013-09-01 09:04:00.000 2013-09-01 17:13:00.000
张三 2013-09-02 00:00:00.000 2013-09-02 09:08:00.000 2013-09-02 17:06:00.000
张三 2013-09-03 00:00:00.000 2013-09-03 09:05:00.000 2013-09-03 17:13:00.000
张三 2013-09-04 00:00:00.000 2013-09-04 09:02:00.000 2013-09-04 17:08:00.000
张三 2013-09-05 00:00:00.000 2013-09-05 09:07:00.000 2013-09-05 17:13:00.000
张三 2013-09-06 00:00:00.000 2013-09-06 09:06:00.000 2013-09-06 17:13:00.000
(6 row(s) affected)
*/