我有一条数据是一个小时一条的,我怎么把他弄成 10分钟一条,规则如下
时间 DUSTA2009-1-5 0:00:00 69.716我想要成
时间 DUSTA2009-1-5 0:00:00 69.716
2009-1-5 10:00:00 69.716
2009-1-5 20:00:00 69.716
2009-1-5 30:00:00 69.7162009-1-5 40:00:00 69.716
2009-1-5 50:00:00 69.716这样的该怎么弄
时间 DUSTA2009-1-5 0:00:00 69.716我想要成
时间 DUSTA2009-1-5 0:00:00 69.716
2009-1-5 10:00:00 69.716
2009-1-5 20:00:00 69.716
2009-1-5 30:00:00 69.7162009-1-5 40:00:00 69.716
2009-1-5 50:00:00 69.716这样的该怎么弄
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([时间] datetime,[DUSTA] numeric(5,3))
insert [tb]
select '2009-1-5 0:00:00',69.716
---查询---
select
dateadd(mi,10*b.number,时间) as 时间,
DUSTA
from
[tb] a,
master..spt_values b
where
b.type='P' and b.number<=5---结果---
时间 DUSTA
------------------------------------------------------ -------
2009-01-05 00:00:00.000 69.716
2009-01-05 00:10:00.000 69.716
2009-01-05 00:20:00.000 69.716
2009-01-05 00:30:00.000 69.716
2009-01-05 00:40:00.000 69.716
2009-01-05 00:50:00.000 69.716(所影响的行数为 6 行)
DECLARE @Tab TABLE(Date DATETIME,Dustra DECIMAL(12,5))INSERT INTO @Tab
SELECT '2009-1-5 ', 69.716
--SQL--
SELECT DATEADD(mi,20, Date),Dustra
FROM @Tab,master..spt_values b
where
b.type='P' and b.number<=5
--Result
Date Dustra
----------------------- ---------------------------------------
2009-01-05 00:20:00.000 69.71600
2009-01-05 00:20:00.000 69.71600
2009-01-05 00:20:00.000 69.71600
2009-01-05 00:20:00.000 69.71600
2009-01-05 00:20:00.000 69.71600
2009-01-05 00:20:00.000 69.71600(6 行受影响)
DECLARE @Tab TABLE(Date DATETIME,Dustra DECIMAL(12,5))INSERT INTO @Tab
SELECT '2009-1-5 ', 69.716
--SQL--
SELECT DATEADD(mi,b.number * 10, Date) Date,Dustra
FROM @Tab,master..spt_values b
where
b.type='P' and b.number<=5
--Result
Date Dustra
----------------------- ---------------------------------------
2009-01-05 00:00:00.000 69.71600
2009-01-05 00:10:00.000 69.71600
2009-01-05 00:20:00.000 69.71600
2009-01-05 00:30:00.000 69.71600
2009-01-05 00:40:00.000 69.71600
2009-01-05 00:50:00.000 69.71600(6 行受影响)