如果 有个 一个table
有数据这样 ttime enter exit high low2012-02-16 00:00:00.000 1.30170 1.30163 1.30175 1.30161
2012-02-16 00:01:00.000 1.30163 1.30160 1.30174 1.30160
2012-02-16 00:02:00.000 1.30160 1.30167 1.30170 1.30160
2012-02-16 00:03:00.000 1.30167 1.30160 1.30173 1.30160
2012-02-16 00:04:00.000 1.30160 1.30145 1.30160 1.30129
2012-02-16 00:05:00.000 1.30145 1.30148 1.30154 1.30143
2012-02-16 00:06:00.000 1.30148 1.30097 1.30165 1.30097
2012-02-16 00:07:00.000 1.30097 1.30100 1.30108 1.30085
2012-02-16 00:08:00.000 1.30100 1.30109 1.30114 1.30093
2012-02-16 00:09:00.000 1.30109 1.30134 1.30140 1.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.000 1.30170 1.30145 1.30175 1.30129
2012-02-16 00:00:10.000 1.30145 1.30134 1.30165 1.30085
不考虑用 cursor的情况
有数据这样 ttime enter exit high low2012-02-16 00:00:00.000 1.30170 1.30163 1.30175 1.30161
2012-02-16 00:01:00.000 1.30163 1.30160 1.30174 1.30160
2012-02-16 00:02:00.000 1.30160 1.30167 1.30170 1.30160
2012-02-16 00:03:00.000 1.30167 1.30160 1.30173 1.30160
2012-02-16 00:04:00.000 1.30160 1.30145 1.30160 1.30129
2012-02-16 00:05:00.000 1.30145 1.30148 1.30154 1.30143
2012-02-16 00:06:00.000 1.30148 1.30097 1.30165 1.30097
2012-02-16 00:07:00.000 1.30097 1.30100 1.30108 1.30085
2012-02-16 00:08:00.000 1.30100 1.30109 1.30114 1.30093
2012-02-16 00:09:00.000 1.30109 1.30134 1.30140 1.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.000 1.30170 1.30145 1.30175 1.30129
2012-02-16 00:00:10.000 1.30145 1.30134 1.30165 1.30085
不考虑用 cursor的情况
解决方案 »
- 能否用一个脚本来生成数据库创建脚本(表、存储过程...)
- 问个查询方式
- 使用企业管理器怎么把存储过程也导出到一个新数据库中?
- MSSQL select 是否会等待 insert update (锁问题)
- 急急急!!!SQL2000关于关键字处理问题!!!在线等~~~
- 在设计数据库表结构时,要考虑哪些影响效率的问题?
- case 分组汇总
- 一个日期相减问题
- 这种序号怎么排序,1.2 1.3 11.5 11.5.2 12.6 12.6.1 13
- 我用delphi做的前台,后台用SQL server 2000.结果报错了:Microsoft][ODBC SQL Server Driver][SQL Server]在此上下文中不允许使用'DDD'
declare @T table
(ttime datetime,enter numeric(6,5),
[exit] numeric(6,5),high numeric(6,5),low numeric(6,5))
insert into @T
select '2012-02-16 00:00:00.000',1.30170,1.30163,1.30175,1.30161 union all
select '2012-02-16 00:01:00.000',1.30163,1.30160,1.30174,1.30160 union all
select '2012-02-16 00:02:00.000',1.30160,1.30167,1.30170,1.30160 union all
select '2012-02-16 00:03:00.000',1.30167,1.30160,1.30173,1.30160 union all
select '2012-02-16 00:04:00.000',1.30160,1.30145,1.30160,1.30129 union all
select '2012-02-16 00:05:00.000',1.30145,1.30148,1.30154,1.30143 union all
select '2012-02-16 00:06:00.000',1.30148,1.30097,1.30165,1.30097 union all
select '2012-02-16 00:07:00.000',1.30097,1.30100,1.30108,1.30085 union all
select '2012-02-16 00:08:00.000',1.30100,1.30109,1.30114,1.30093 union all
select '2012-02-16 00:09:00.000',1.30109,1.30134,1.30140,1.30108--帮你写个思路
select MAX(high),MIN(low) from @T
GROUP BY datediff(mi,'2012-02-16 00:00:00.000',ttime)/5/*
1.30175 1.30129
1.30165 1.30085
*/
如果 有个 一个table
有数据这样 ttime enter exit high low2012-02-16 00:00:00.000 1.30170 1.30163 1.30175 1.30161
2012-02-16 00:01:00.000 1.30163 1.30160 1.30174 1.30160
2012-02-16 00:02:00.000 1.30160 1.30167 1.30170 1.30160
2012-02-16 00:03:00.000 1.30167 1.30160 1.30173 1.30160
2012-02-16 00:04:00.000 1.30160 1.30145 1.30160 1.30129
2012-02-16 00:05:00.000 1.30145 1.30148 1.30154 1.30143
2012-02-16 00:06:00.000 1.30148 1.30097 1.30165 1.30097
2012-02-16 00:07:00.000 1.30097 1.30100 1.30108 1.30085
2012-02-16 00:08:00.000 1.30100 1.30109 1.30114 1.30093
2012-02-16 00:09:00.000 1.30109 1.30134 1.30140 1.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.000 1.30170 1.30145 1.30175 1.30129
2012-02-16 00:00:10.000 1.30145 1.30134 1.30165 1.30085不考虑用 cursor的情况
*/
go
if OBJECT_ID('tbl')is not null
drop table tbl
go
create table tbl(
ttime datetime,
enter numeric(8,5),
[exit] numeric(8,5),
high numeric(8,5),
low numeric(8,5)
)
go
insert tbl
select '2012-02-16 00:00:00.000',1.30170,1.30163,1.30175,1.30161 union all
select '2012-02-16 00:01:00.000',1.30163,1.30160,1.30174,1.30160 union all
select '2012-02-16 00:02:00.000',1.30160,1.30167,1.30170,1.30160 union all
select '2012-02-16 00:03:00.000',1.30167,1.30160,1.30173,1.30160 union all
select '2012-02-16 00:04:00.000',1.30160,1.30145,1.30160,1.30129 union all
select '2012-02-16 00:05:00.000',1.30145,1.30148,1.30154,1.30143 union all
select '2012-02-16 00:06:00.000',1.30148,1.30097,1.30165,1.30097 union all
select '2012-02-16 00:07:00.000',1.30097,1.30100,1.30108,1.30085 union all
select '2012-02-16 00:08:00.000',1.30100,1.30109,1.30114,1.30093 union all
select '2012-02-16 00:09:00.000',1.30109,1.30134,1.30140,1.30108select *from tbl
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
print @min
while @minid<=@id
begin
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
goexec p_tt/*
结果:
ttime enter enter high low
2012-02-16 00:05:00.000 1.30170 1.30145 1.30175 1.30129
ttime enter enter high low
2012-02-16 00:10:00.000 1.30145 1.30134 1.30165 1.30085
*/
--我不知道在存储过程中怎么把这两个数据给union all起来
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
goexec p_tt/*
结果:
ttime enter exit high low
2012-02-16 00:05:00.000 1.30170 1.30145 1.30175 1.30129
2012-02-16 00:10:00.000 1.30145 1.30134 1.30165 1.30085
*/
--我在存储过程中建了一个临时表,这样可以实现.
declare @T table
(ttime datetime,enter numeric(6,5),
[exit] numeric(6,5),high numeric(6,5),low numeric(6,5))
insert into @T
select '2012-02-16 00:00:00.000',1.30170,1.30163,1.30175,1.30161 union all
select '2012-02-16 00:01:00.000',1.30163,1.30160,1.30174,1.30160 union all
select '2012-02-16 00:02:00.000',1.30160,1.30167,1.30170,1.30160 union all
select '2012-02-16 00:03:00.000',1.30167,1.30160,1.30173,1.30160 union all
select '2012-02-16 00:04:00.000',1.30160,1.30145,1.30160,1.30129 union all
select '2012-02-16 00:05:00.000',1.30145,1.30148,1.30154,1.30143 union all
select '2012-02-16 00:06:00.000',1.30148,1.30097,1.30165,1.30097 union all
select '2012-02-16 00:07:00.000',1.30097,1.30100,1.30108,1.30085 union all
select '2012-02-16 00:08:00.000',1.30100,1.30109,1.30114,1.30093 union all
select '2012-02-16 00:09:00.000',1.30109,1.30134,1.30140,1.30108--帮你写个思路
select
(select enter from @t where ttime=a.c4),
(select [exit] from @t where ttime=a.c3),
c1,c2
from
(select max(high) c1 ,min(low) c2,max(ttime) c3,min(ttime) as c4 from @t
group by datediff(mi,'2012-02-16 00:00:00.000',ttime)/5
) a/*
1.30170 1.30145 1.30175 1.30129
1.30145 1.30134 1.30165 1.30085
*/
谢谢 我再试试
(select penter from #temp where ttime=a.c4) as penter,
(select pexit from #temp where ttime=a.c3) as pexit,
c1 as phigh,c2 as plow
from
(select dateadd(minute,
(datediff(minute,convert(char(10),ttime,120),ttime)/5+1)*5, --5分钟,是30分钟的话,将5改为30
convert(char(10),ttime,120)) as ttime,max(phigh) c1 ,min(plow) c2,max(ttime) c3,min(ttime) as c4 from #temp
group by dateadd(minute,
(datediff(minute,convert(char(10),ttime,120),ttime)/5+1)*5, --5分钟,是30分钟的话,将5改为30
convert(char(10),ttime,120))
) a