时间 dusta
2009-3-29 10:30:00 117.95
2009-3-29 11:00:00 119.45
2009-3-29 11:30:00 118.52
以下都是 半个小时一条数据
我样弄成这样 10分钟一条的时间 dusta
2009-3-29 10:30:00 117.95
2009-3-29 10:40:00 117.95
2009-3-29 10:50:00 117.95
2009-3-29 11:00:00 119.45
2009-3-29 11:10:00 119.45
2009-3-29 11:20:00 119.45
2009-3-29 11:30:00 118.52
2009-3-29 11:40:00 118.52
2009-3-29 11:50:00 118.52这样的数据该怎查询
2009-3-29 10:30:00 117.95
2009-3-29 11:00:00 119.45
2009-3-29 11:30:00 118.52
以下都是 半个小时一条数据
我样弄成这样 10分钟一条的时间 dusta
2009-3-29 10:30:00 117.95
2009-3-29 10:40:00 117.95
2009-3-29 10:50:00 117.95
2009-3-29 11:00:00 119.45
2009-3-29 11:10:00 119.45
2009-3-29 11:20:00 119.45
2009-3-29 11:30:00 118.52
2009-3-29 11:40:00 118.52
2009-3-29 11:50:00 118.52这样的数据该怎查询
declare @TB table(date datetime,dusta float)
insert into @TB
select '2009-3-29 10:30:00', 117.95 union all
select '2009-3-29 11:00:00', 119.45 union all
select '2009-3-29 11:30:00', 118.52
select dateadd(n,10,date),dusta from @TB where date<=dateadd(n,10,date)
union all
select dateadd(n,20,date),dusta from @TB where date<=dateadd(n,20,date)
union all
select dateadd(n,30,date),dusta from @TB where date<=dateadd(n,30,date)(No column name) dusta
2009-03-29 10:40:00.000 117.95
2009-03-29 11:10:00.000 119.45
2009-03-29 11:40:00.000 118.52
2009-03-29 10:50:00.000 117.95
2009-03-29 11:20:00.000 119.45
2009-03-29 11:50:00.000 118.52
2009-03-29 11:00:00.000 117.95
2009-03-29 11:30:00.000 119.45
2009-03-29 12:00:00.000 118.52
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2009-05-26 08:59:48
---------------------------------
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
go
CREATE TABLE [tb] (时间 DATETIME,dusta NUMERIC(5,2))
INSERT INTO [tb]
SELECT '2009-3-29 10:30:00',117.95 UNION ALL
SELECT '2009-3-29 11:00:00',119.45 UNION ALL
SELECT '2009-3-29 11:30:00',118.52select * from [tb]with
wang as (select mint=min(时间),maxt=max(时间) from tb union all
select 时间=dateadd(mi,10,mint),maxt from wang where dateadd(mi,10,mint)<=maxt)select mint,dusta=(select top 1 dusta from tb where 时间<=t.mint order by 时间 desc) from wang tmint dusta
2009-03-29 10:30:00.000 117.95
2009-03-29 10:40:00.000 117.95
2009-03-29 10:50:00.000 117.95
2009-03-29 11:00:00.000 119.45
2009-03-29 11:10:00.000 119.45
2009-03-29 11:20:00.000 119.45
2009-03-29 11:30:00.000 118.52
什么意思
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
go
CREATE TABLE [tb] (时间 DATETIME,dusta NUMERIC(5,2))
INSERT INTO [tb]
SELECT '2009-3-29 10:30:00',117.95 UNION ALL
SELECT '2009-3-29 11:00:00',119.45 UNION ALL
SELECT '2009-3-29 11:30:00',118.52select 时间,dusta from
(
select 时间,
时间1 = dateadd(minute,10,时间),
时间2 = dateadd(minute,20,时间),
dusta
from tb
)T
UNION ALL
select 时间1,dusta from
(
select 时间,
时间1 = dateadd(minute,10,时间),
时间2 = dateadd(minute,20,时间),
dusta
from tb
)T
UNION ALL
select 时间2,dusta from
(
select 时间,
时间1 = dateadd(minute,10,时间),
时间2 = dateadd(minute,20,时间),
dusta
from tb
)T
ORDER BY 时间/*
2009-03-29 10:30:00.000 117.95
2009-03-29 10:40:00.000 117.95
2009-03-29 10:50:00.000 117.95
2009-03-29 11:00:00.000 119.45
2009-03-29 11:10:00.000 119.45
2009-03-29 11:20:00.000 119.45
2009-03-29 11:30:00.000 118.52
2009-03-29 11:40:00.000 118.52
2009-03-29 11:50:00.000 118.52
*/drop table tb
insert @t values('2009-3-29 11:00:00',119.45)
insert @t values('2009-3-29 11:30:00',118.52)--查询
select 时间=时间,dusta from @t union all
select 时间=dateadd(mi,10,时间),dusta from @t union all
select 时间=dateadd(mi,20,时间),dusta from @t order by 时间
/*
时间 dusta
----------------------- ---------------------------------------
2009-03-29 10:30:00.000 117.95
2009-03-29 10:40:00.000 117.95
2009-03-29 10:50:00.000 117.95
2009-03-29 11:00:00.000 119.45
2009-03-29 11:10:00.000 119.45
2009-03-29 11:20:00.000 119.45
2009-03-29 11:30:00.000 118.52
2009-03-29 11:40:00.000 118.52
2009-03-29 11:50:00.000 118.52(9 行受影响)
*/