a1 a2
200812 2008-12-26 16:49:39.310 NULL NULL C35547ECE8DA4DE4A768187D2ACF43F7 0000002326
200812 2008-12-26 16:49:39.450 NULL NULL C35547ECE8DA4DE4A768187D2ACF43F7 0000010760
200812 2008-12-26 16:49:39.467 NULL NULL 93682250EF0040239846FF4AC117072C 0000010777
200812 2008-12-26 16:49:39.607 NULL NULL 93682250EF0040239846FF4AC117072C 0000002329
200812 2008-12-26 16:49:39.607 NULL NULL C35547ECE8DA4DE4A768187D2ACF43F7 0000002332
200812 2008-12-26 16:49:39.750 NULL NULL C35547ECE8DA4DE4A768187D2ACF43F7 0000010784
200812 2008-12-26 16:49:39.763 NULL NULL 93682250EF0040239846FF4AC117072C 0000010786
200812 2008-12-26 16:49:39.903 NULL NULL 93682250EF0040239846FF4AC117072C 0000002333
200812 2008-12-26 16:49:39.903 NULL NULL C35547ECE8DA4DE4A768187D2ACF43F7 0000002335
200812 2008-12-26 16:49:40.043 NULL NULL C35547ECE8DA4DE4A768187D2ACF43F7 0000002320
如上表,
我想批量更新 a2的日期,更新为 本月1-26号任意一天,不包括周六、周日; 时间更新为8:00-16:00 之间不包括 12:00-14:00
一条语句能实现么。
200812 2008-12-26 16:49:39.310 NULL NULL C35547ECE8DA4DE4A768187D2ACF43F7 0000002326
200812 2008-12-26 16:49:39.450 NULL NULL C35547ECE8DA4DE4A768187D2ACF43F7 0000010760
200812 2008-12-26 16:49:39.467 NULL NULL 93682250EF0040239846FF4AC117072C 0000010777
200812 2008-12-26 16:49:39.607 NULL NULL 93682250EF0040239846FF4AC117072C 0000002329
200812 2008-12-26 16:49:39.607 NULL NULL C35547ECE8DA4DE4A768187D2ACF43F7 0000002332
200812 2008-12-26 16:49:39.750 NULL NULL C35547ECE8DA4DE4A768187D2ACF43F7 0000010784
200812 2008-12-26 16:49:39.763 NULL NULL 93682250EF0040239846FF4AC117072C 0000010786
200812 2008-12-26 16:49:39.903 NULL NULL 93682250EF0040239846FF4AC117072C 0000002333
200812 2008-12-26 16:49:39.903 NULL NULL C35547ECE8DA4DE4A768187D2ACF43F7 0000002335
200812 2008-12-26 16:49:40.043 NULL NULL C35547ECE8DA4DE4A768187D2ACF43F7 0000002320
如上表,
我想批量更新 a2的日期,更新为 本月1-26号任意一天,不包括周六、周日; 时间更新为8:00-16:00 之间不包括 12:00-14:00
一条语句能实现么。
--这个关键是产生随机时间declare @daydate datetime,@day int,@hour int declare @table table(id int identity(1,1),x datetime)
declare @m int,@mm int
set @m=1
while @m<30000
begin
select @day=cast(rand()*100000 as int)%25
select @hour=cast(rand()*20 as int)
select @daydate=dateadd(hour,@hour,dateadd(d,-@day,getdate()))
if(datediff(d,@daydate,getdate())<27 and datediff(d,@daydate,getdate())>1)
begin
if(datepart(weekday,@daydate)-1<>6 or datepart(weekday,@daydate)-1<>-1)
begin
if((datepart(hour,@daydate)>=8 and datepart(hour,@daydate)<12) or(datepart(hour,@daydate)>=14 and datepart(hour,@daydate)<16))
insert into @table select dateadd(ms,rand()*60,dateadd(s,rand()*600,@daydate))
end
end
set @m=@m+1
endselect * from @table--
/*
--自己随便连接UPDATE下
*/
楼主不是要一句SQL吗?
下面这个应该算一句吧!
--测试数据
declare @t table (a1 varchar(10),a2 datetime)
insert @t select '200812' ,'2008-12-26 16:49:39.310'
union all select '200812' ,'2008-12-26 16:49:39.450'
union all select '200812' ,'2008-12-26 16:49:39.467'
union all select '200812' ,'2008-12-26 16:49:39.607'
union all select '200812' ,'2008-12-26 16:49:39.607'
union all select '200812' ,'2008-12-26 16:49:39.750'
union all select '200812' ,'2008-12-26 16:49:39.763'
union all select '200812' ,'2008-12-26 16:49:39.903'
union all select '200812' ,'2008-12-26 16:49:39.903'
union all select '200812' ,'2008-12-26 16:49:40.043'
--更新
declare @a datetime
update @t set @a=case when dateadd(day,0-cast(rand()*26 as int),isnull(@a,a2))<'2008-12-01'
then dateadd(day,0-cast(rand()*26 as int),a2)
else dateadd(day,0-cast(rand()*26 as int),isnull(@a,a2)) end,
@a=dateadd(hour,cast(rand()*8+8 as int),@a),
a2=case when datepart(weekday,@a) not in (1,7)
and datepart(hour,@a) not between 12 and 14 then @a else a2 end