日期:2014-05-17 浏览次数:20521 次
create table #aaaa
(
datet datetime,
value varchar(11)
);
insert into #aaaa values ('2013-10-23 09:00','0.5');
insert into #aaaa values ('2013-10-23 09:05','0.3');
insert into #aaaa values ('2013-10-23 09:10','0.5');
insert into #aaaa values ('2013-10-23 09:15','0.6');
insert into #aaaa values ('2013-10-23 09:20','0.1');
insert into #aaaa values ('2013-10-23 09:20','0.1');
insert into #aaaa values ('2013-10-23 09:30','0.2');
insert into #aaaa values ('2013-10-23 10:00','0.9');
insert into #aaaa values ('2013-10-23 10:05','0.3');
insert into #aaaa values ('2013-10-23 10:10','0.3');
insert into #aaaa values ('2013-10-23 10:20','0.3');
select * from #aaaa;
drop table #aaaa;
--初始化100条数据
CREATE TABLE #t (datet DATETIME,id INT IDENTITY(1,1))
INSERT INTO #t(datet) VALUES('2013-10-23 09:00')
DECLARE @i INT
SET @i=1
WHILE @i<=100
BEGIN
INSERT INTO #t(datet)
SELECT DATEADD(mi,5,datet)
FROM #t
WHERE @i=id+1
SET @i=@i+1
END
--DROP TABLE #T
SELECT *
FROM #aaaa a right JOIN #t t ON t.datet = a.datet
/*
datet value datet id
----------------------- ----------- ----------------------- -----------
2013-10-23 09:00:00.000 0.5 2013-10-23 09:00:00.000 1
2013-10-23 09:05:00.000 0.3 2013-10-23 09:05:00.000 2
2013-10-23 09:10:00.000 0.5 2013-10-23 09:10:00.000 3
2013-10-23 09:15:00.000 0.6 2013-10-23 09:15:00.000 4
2013-10-23 09:20:00.000 0.1 2013-10-23 09:20:00.000 5
2013-10-23 09:20:00.000 0.1 2013-10-23 09:2