是这样的,我是每天每一分钟一条数据。
如:
tmid ssid rectime power vart tmpt press energy
10000 1 2010-3-20 1:01:00 11 22 33 44 55
10000 1 2010-3-20 1:02:00 22 33 44 55 66
10000 2 2010-3-20 1:02:00 22 33 44 55 66
10003 1 2010-3-20 1:02:00 22 33 44 55 66这样下来,每天的数据可能不完整。我其实目的就是补充一些假的数据上去,让每天的分钟数据完整。为了不太假,所以想把10天以前的数据补充上来。
如把10000,1,2010-3-10 1:01:00的数据(power,vart,tmpt,press,energy)补充给
10000,1,2010-3-20 1:01:00
如:
tmid ssid rectime power vart tmpt press energy
10000 1 2010-3-20 1:01:00 11 22 33 44 55
10000 1 2010-3-20 1:02:00 22 33 44 55 66
10000 2 2010-3-20 1:02:00 22 33 44 55 66
10003 1 2010-3-20 1:02:00 22 33 44 55 66这样下来,每天的数据可能不完整。我其实目的就是补充一些假的数据上去,让每天的分钟数据完整。为了不太假,所以想把10天以前的数据补充上来。
如把10000,1,2010-3-10 1:01:00的数据(power,vart,tmpt,press,energy)补充给
10000,1,2010-3-20 1:01:00
tmid ssid rectime power vart tmpt press energy
10000 1 2010-3-20 1:01:00 11 22 33 44 55
10000 1 2010-3-20 1:02:00 22 33 44 55 66
10000 1 2010-3-20 1:04:00 22 33 44 55 66那么少了一条10000 1 2010-3-20 1:03:00的数据。我就想把10000 1 2010-3-10 1:03:00的数据复制给
10000 1 2010-3-20 1:03:00
刚才发信息给你你又没回,要得比较急 呵呵
insert into tb select tmid ,ssid ,dateadd(dd,10,rectime) ,power ,vart ,tmpt ,press, energy from tb t where tmid=10000 ssid=1 and dateadd(dd,10,rectime) not in (select rectime from tb)
select tmid ,ssid ,dateadd(dd,10,rectime) ,power ,vart ,tmpt ,press, energy from tb t
where not exists (select 1 rectime from tb dateadd(dd,10,t.rectime)=rectime and t.ssid=b.ssid and t.tmid=tmid )
-- Author : htl258(Tony)
-- Date : 2010-04-06 10:27:05
-- Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
-- Mar 29 2009 10:27:29
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([tmid] INT,[ssid] INT,[rectime] DATETIME,[power] INT,[vart] INT,[tmpt] INT,[press] INT,[energy] INT)
INSERT [tb]
SELECT 10000,1,N'2010-3-10 1:03:00',12,13,14,15,16 UNION ALL
SELECT 10000,1,N'2010-3-20 1:01:00',11,22,33,44,55 UNION ALL
SELECT 10000,1,N'2010-3-20 1:02:00',22,33,44,55,66 UNION ALL
SELECT 10000,1,N'2010-3-20 1:04:00',22,33,44,55,66 UNION ALL
SELECT 10001,2,N'2010-3-10 1:03:00',12,13,14,15,16 UNION ALL
SELECT 10001,2,N'2010-3-20 1:01:00',11,22,33,44,55 UNION ALL
SELECT 10001,2,N'2010-3-20 1:02:00',22,33,44,55,66 UNION ALL
SELECT 10001,2,N'2010-3-20 1:04:00',22,33,44,55,66GO
--SELECT * FROM [tb]-->SQL查询如下:alter proc sp_copydata
@begdate datetime --起始日期
,@enddate datetime --截止日期
,@n int --复制前n天的记录
as
insert tb
select tmid,ssid,dt,[power],vart,tmpt,press,energy
from (
select [dt]=DATEADD(mi,a.number,@begdate)
from (
select number from master..spt_values
where type='p' and number <= datediff(MI,@begdate,@enddate)
) a
where not exists(select 1 from tb where [rectime]=DATEADD(mi,a.number,@begdate))
) a
join tb b on a.dt-@n=b.rectime
GO
--调用过程:
exec sp_copydata
@begdate='2010-3-20 1:01:00' --大于等于这个时间
,@enddate ='2010-3-20 1:04:00' --小于等于这个时间
,@n=10 --更新到10天前的记录
select * from tb
/*
tmid ssid rectime power vart tmpt press energy
----------- ----------- ----------------------- ----------- ----------- ----------- ----------- -----------
10000 1 2010-03-10 01:03:00.000 12 13 14 15 16
10000 1 2010-03-20 01:01:00.000 11 22 33 44 55
10000 1 2010-03-20 01:02:00.000 22 33 44 55 66
10000 1 2010-03-20 01:04:00.000 22 33 44 55 66
10001 2 2010-03-10 01:03:00.000 12 13 14 15 16
10001 2 2010-03-20 01:01:00.000 11 22 33 44 55
10001 2 2010-03-20 01:02:00.000 22 33 44 55 66
10001 2 2010-03-20 01:04:00.000 22 33 44 55 66
10000 1 2010-03-20 01:03:00.000 12 13 14 15 16
10001 2 2010-03-20 01:03:00.000 12 13 14 15 16(10 行受影响)
*/我测试有效啊
如tmid=10000下包括 ssid=1,ssid=2,ssid=3
tmid=10001下包括 ssid=1,ssid=2,ssid=3,ssid=4
表名:data
字段:
tmid-int
ssid-int
rectime-datetime
datatype-int
power-float
vart-float
tmpt-float
press-float
energy-float
--按照分钟(10*24*60=14440)insert into tb select tmid ,ssid ,dateadd(mi,14440,rectime) ,power ,vart ,tmpt ,press, energy from tb t where left(convert(varchar(16),dateadd(dd,14440,rectime),120),16) not in (select left(convert(varchar(16),rectime,120),16) from tb)--按照分钟(10*24=240)insert into tb select tmid ,ssid ,dateadd(hh,240,rectime) ,power ,vart ,tmpt ,press, energy from tb t where left(convert(varchar(16),dateadd(hh,240,rectime),13) not in (select left(convert(varchar(13),rectime,120),16) from tb)