22,207
社区成员
发帖
与我相关
我的任务
分享
--用s_time和该时间0点计算分钟数,然后整除5
--这样只是组可能不是从1开始,但是每5分钟一个组是正确的
UPDATE 表 SET period=DATEDIFF(mi,CONVERT(DATE,s_time),s_time)/5
DECLARE @i int
DECLARE @time_start smalldatetime
DECLARE @time_end smalldatetime
SET @i=1
SET @time_start= CONVERT(smalldatetime,SUBSTRING('20170909 00:00:00',1,120))
WHILE @i<=288
BEGIN SET @time_end = DATEADD(mi,*5,@time_start)
UPDATE CAR_FLOW
SET PERIOD = @i WHERE S_TIME BETWEEN @time_start AND @time_end
SET @time_start = @time_end
SET @i+=1
END
CREATE clustered INDEX ix_CAR_FLOW_S_TIME ON CAR_FLOW(S_TIME)
ALTER TABLE CAR_FLOW ADD PERIOD
DECLARE @i int
DECLARE @time_start smalldatetime
DECLARE @time smalldatetime
SET @i=1
SET @time_start= CONVERT(smalldatetime,SUBSTRING('20170909 00:00:00',1,120))
WHILE @i<=288
BEGIN
UPDATE CAR_FLOW
SET PERIOD = @i WHERE S_TIME BETWEEN DATEADD(mi,(@i-1)*5,@time_start) AND DATEADD(mi,@i*5,@time_start)
SET @i+=1
END