try:
1:先生成结果表再改
id workno date time
1 wn1 20030901 07:21
2 wn1 20030902 07:21
...
22 wn1 20030930 07:21
23 wn2 20030901 07:21
24 wn2 20030902 07:21
...
44 wn2 20030930 07:21再用游标循环,在循环中用30*rand()来代替后两位。
当然效率比较差一点,看有没有更好的办法。
1:先生成结果表再改
id workno date time
1 wn1 20030901 07:21
2 wn1 20030902 07:21
...
22 wn1 20030930 07:21
23 wn2 20030901 07:21
24 wn2 20030902 07:21
...
44 wn2 20030930 07:21再用游标循环,在循环中用30*rand()来代替后两位。
当然效率比较差一点,看有没有更好的办法。
declare @tb table(id int identity(1,1),dt datetime)
insert into @tb(dt)select top 10000 dateadd(ss,rand(id)*10000,'07:21') from sysobjects order by newid()select * from @tb
除非放在循环中,即一楼的办法。
declare @打卡资料表 table(id int identity(1,1),workno varchar(10),date datetime,time datetime)declare @r int
insert into @打卡资料表
/*--先按固定时间插入资料,我这里改为直接插入
Select workNo,[date],'07:21' from calendar,workers
where convert(char(6),[date],112) = '200309' and IsWorkday = 1 -- 应上班日期
--*/
select '01','2003-01-01','07:21'
union all select '02','2003-01-01','07:21'
union all select '03','2003-01-01','07:21'
union all select '04','2003-01-01','07:21'
union all select '05','2003-01-01','07:21'
union all select '06','2003-01-01','07:21'
union all select '07','2003-01-01','07:21'--得到要处理的记录数
set @r=@@rowcount--创建得到随机时间的临时表
create table #tb(id int identity(1,1),dt datetime)--生成随机时间
declare @sql varchar(8000)
set @sql='insert into #tb(dt) select top '+cast(@r as varchar)+'
dateadd(ss,rand(a.id)*10000,''07:21'')
from(select top 100 id from sysobjects) a,
(select top 100 id from sysobjects) b,
(select top 100 id from sysobjects) c
order by newid()
'
exec(@sql)--更新时间
update @打卡资料表 set time=b.dt
from @打卡资料表 a inner join #tb b on a.id=b.id--显示结果
select * from @打卡资料表
--删除临时表
drop table #tb
--生成随机时间
declare @sql varchar(8000)
set @sql='insert into #tb(dt) select top '+cast(@r as varchar)+'
dateadd(ss,rand(a.id)*3000,''07:30'')
from(select top 100 id from sysobjects) a,
(select top 100 id from sysobjects) b,
(select top 100 id from sysobjects) c
order by newid()
用
dateadd(minute,rand(a.id)*30,'07:30') 或
dateadd(second,rand(a.id)*1800,'07:30') 来控制 时间在07:30-08:00之间
select id,workno,[date]=convert(varchar(10),[date],120),[time]=convert(varchar(5),[time],108) from @打卡资料表--我的电脑上的结果:
id workno date time
----------- ---------- ---------- -----
1 01 2003-01-01 08:06
2 02 2003-01-01 07:37
3 03 2003-01-01 08:04
4 04 2003-01-01 08:04
5 05 2003-01-01 08:05
6 06 2003-01-01 08:05
7 07 2003-01-01 07:36(所影响的行数为 7 行)
declare @sql varchar(8000)
set @sql='insert into #tb(dt) select top '+cast(@r as varchar)+'
dateadd(ss,rand(a.id)*3000,''07:30'')
from(select top 100 id from sysobjects) a, --减少top 100的值
(select top 100 id from sysobjects) b, --减少top 100的值
(select top 100 id from sysobjects) c --减少top 100的值,或取消
order by newid()'