SQL

SQL求救
如果 有个 一个table  


有数据这样 

  ttime enter exit high low

2012-02-16 00:00:00.0001.301701.301631.301751.30161
2012-02-16 00:01:00.0001.301631.301601.301741.30160
2012-02-16 00:02:00.0001.301601.301671.301701.30160
2012-02-16 00:03:00.0001.301671.301601.301731.30160
2012-02-16 00:04:00.0001.301601.301451.301601.30129
2012-02-16 00:05:00.0001.301451.301481.301541.30143
2012-02-16 00:06:00.0001.301481.300971.301651.30097
2012-02-16 00:07:00.0001.300971.301001.301081.30085
2012-02-16 00:08:00.0001.301001.301091.301141.30093
2012-02-16 00:09:00.0001.301091.301341.301401.30108



要是想用一个sql query 


得到这样的结果  


00:00-00:04 一组  
00:05-00:09 一组

group 起来 enter 是 时间最5分钟内最早的值 exit 是 最5分钟内最晚的值 high 是5分钟内最大的值 low是 5分钟之类最小的值


结果 应该是 

2012-02-16 00:00:05.0001.30170 1.30145 1.30175 1.30129
2012-02-16 00:00:10.0001.30145 1.30134 1.30165 1.30085


不考虑用 cursor的情况

[解决办法]

SQL code

declare @T table (ttime datetime,enter numeric(6,5),[exit] numeric(6,5),high numeric(6,5),low numeric(6,5))insert into @Tselect '2012-02-16 00:00:00.000',1.30170,1.30163,1.30175,1.30161 union allselect '2012-02-16 00:01:00.000',1.30163,1.30160,1.30174,1.30160 union allselect '2012-02-16 00:02:00.000',1.30160,1.30167,1.30170,1.30160 union allselect '2012-02-16 00:03:00.000',1.30167,1.30160,1.30173,1.30160 union allselect '2012-02-16 00:04:00.000',1.30160,1.30145,1.30160,1.30129 union allselect '2012-02-16 00:05:00.000',1.30145,1.30148,1.30154,1.30143 union allselect '2012-02-16 00:06:00.000',1.30148,1.30097,1.30165,1.30097 union allselect '2012-02-16 00:07:00.000',1.30097,1.30100,1.30108,1.30085 union allselect '2012-02-16 00:08:00.000',1.30100,1.30109,1.30114,1.30093 union allselect '2012-02-16 00:09:00.000',1.30109,1.30134,1.30140,1.30108--帮你写个思路select MAX(high),MIN(low) from @TGROUP BY datediff(mi,'2012-02-16 00:00:00.000',ttime)/5/*1.30175                                 1.301291.30165                                 1.30085*/
[解决办法]
go
if OBJECT_ID('p_tt')is not null
drop proc p_tt
go
create proc p_tt
as
declare @min datetime,@id int,@minid int
select @min=MIN(ttime) from tbl
select @id=COUNT(*) from tbl
set @minid=1
create table #T(
ttime datetime,
enter numeric(8,5),
[exit] numeric(8,5),
high numeric(8,5),
low numeric(8,5)
)
print @min
while @minid<=@id
begin
insert into #T
select distinct DATEADD(MI,5,@min) as ttime,(select enter from tbl where ttime=@min) as enter,
(select [exit] from tbl where ttime=DATEADD(mi,4,@min)) as enter,
(select max(high) from tbl where ttime between @min and DATEADD(mi,4,@min)) as high,
(select min(low) from tbl where ttime between @min and DATEADD(mi,4,@min)) as low
from tbl
set @minid=@minid+5
set @min=dateadd(mi,5,@min)
end
select *from #T
go

exec p_tt

/*
结果：
ttimeenterexithighlow
2012-02-16 00:05:00.0001.301701.301451.301751.30129
2012-02-16 00:10:00.0001.301451.301341.301651.30085
*/
--我在存储过程中建了一个临时表,这样可以实现.