SQL2000中，求以分钟记录的数据按小时查询，并求快速查询中值的方法

[hl521528522]

现有表a，字段dt,a1,a2,a3，数据如下：
dt a1 a2 a3
2010-11-15 10:04:00 4 5 1
2010-11-15 10:08:00 6 2 5
2010-11-15 10:14:00 3 6 6
2010-11-15 10:44:00 4 11 8

2010-11-15 11:07:00 12 5 9
2010-11-15 11:09:00 15 7 11
2010-11-15 11:23:00 4 1 3
2010-11-15 11:36:00 7 9 6
2010-11-15 11:55:00 9 6 3

2010-11-15 12:15:00 23 4 1
2010-11-15 12:32:00 11 5 4
2010-11-15 12:47:00 9 7 7
希望查询所得数据如下：
dt a1 a2 a3
2010-11-15 10:00:00~2010-11-15 11:00:00 4 6 5
2010-11-15 11:00:00~2010-11-15 12:00:00 9 5.6 6.4
2010-11-15 12:00:00~2010-11-15 13:00:00 11 5.33 4
即将上述数据以小时为时间段（表中精确到分钟记录数据）取出a1的中值，a2,a3的平均值

自己写的查询语句，求中值用的是排序取中间值的方法，数据多的时候查询速度就很慢...求大大提供更好的方法~
SQL2000的~~~

[hl521528522]

取中值的时候用了三次比较来排序...

[-晴天]

create table tb(dt datetime,a1 int,a2 int,a3 int)
insert into tb select '2010-11-15 10:04:00',4,5,1
union all select '2010-11-15 10:08:00',6,2,5
union all select '2010-11-15 10:14:00',3,6,6
union all select '2010-11-15 10:44:00',4,11,8

union all select '2010-11-15 11:07:00',12,5,9
union all select '2010-11-15 11:09:00',15,7,11
union all select '2010-11-15 11:23:00',4,1,3
union all select '2010-11-15 11:36:00',7,9,6
union all select '2010-11-15 11:55:00',9,6,3

union all select '2010-11-15 12:15:00',23,4,1
union all select '2010-11-15 12:32:00',11,5,4
union all select '2010-11-15 12:47:00',9,7,7
go
select b.dt+':00:00' as dt,a.a1,abs(a.a1-b.a1)dlta,b.a2,b.a3 into # from(
select convert(varchar(13),dt,120)as dt,avg(convert(decimal(10,2),a1))a1 ,avg(convert(decimal(10,2),a2))a2 ,avg(convert(decimal(10,2),a3))a3 from tb group by convert(varchar(13),dt,120)
)b inner join tb a on b.dt=convert(varchar(13),a.dt,120)
select distinct a.dt,a.a1,a.a2,a.a3 from # a inner join (select dt,min(dlta)as dlta from # group by dt)b on a.dt=b.dt and a.dlta=b.dlta
go
drop table tb,#
/*
dt                  a1          a2                                      a3
------------------- ----------- --------------------------------------- ---------------------------------------
2010-11-15 10:00:00 4           6.000000                                5.000000
2010-11-15 11:00:00 9           5.600000                                6.400000
2010-11-15 12:00:00 11          5.333333                                4.000000

(3 行受影响)

*/

[dawugui]

[Quote=引用楼主 hl521528522 的回复:]
现有表a，字段dt,a1,a2,a3，数据如下：
dt a1 a2 a3
2010-11-15 10:04:00 4 5 1
2010-11-15 10:08:00 6 2 5
2010-11-15 10:14:00 3 6 6
2010-11-15 10:44:00 4 11 8

2010-11-15 11:07:00 12 5 9
……
[/Quote]
方法就是我那天给你的方法，不过你这样的需求，估计查询速度快不起来。

[hl521528522]

米办法啊...公司就是要这么改...原来a1字段也只要查询平均值就可以，现在想要改成查询中值，郁闷啊

[dawugui]

create table tb(dt datetime,a1 int,a2 int,a3 int)
insert into tb select '2010-11-15 10:04:00',4,5,1
union all select '2010-11-15 10:08:00',6,2,5
union all select '2010-11-15 10:14:00',3,6,6
union all select '2010-11-15 10:44:00',4,11,8

union all select '2010-11-15 11:07:00',12,5,9
union all select '2010-11-15 11:09:00',15,7,11
union all select '2010-11-15 11:23:00',4,1,3
union all select '2010-11-15 11:36:00',7,9,6
union all select '2010-11-15 11:55:00',9,6,3

union all select '2010-11-15 12:15:00',23,4,1
union all select '2010-11-15 12:32:00',11,5,4
union all select '2010-11-15 12:47:00',9,7,7
go

select m.dt + ':00:00~' + convert(varchar(19),dateadd(hh,1,cast(m.dt + ':00:00' as datetime)),120) dt,
       cast(avg(n.a1*1.0) as decimal(18,2)) a1,
       m.a2,
       m.a3
from
(
  select convert(varchar(13),dt,120) dt , count(1) cnt , cast(avg(a2*1.0) as decimal(18,2)) a2, cast(avg(a3*1.0) as decimal(18,2)) a3 from tb group by convert(varchar(13),dt,120)
) m,
(
  select convert(varchar(13),dt,120) dt , a1 , px = (select count(1) from tb where datediff(hh,dt,t.dt) = 0 and (a1 < t.a1 or (a1 = t.a1 and dt < t.dt))) + 1 from tb t
) n
where m.dt = n.dt and ((m.cnt % 2 = 1 and n.px = m.cnt / 2 + 1) or (m.cnt % 2 = 0 and (n.px = m.cnt / 2 + 1 or n.px = m.cnt / 2)))
group by m.dt + ':00:00~' + convert(varchar(19),dateadd(hh,1,cast(m.dt + ':00:00' as datetime)),120),m.a2,m.a3

drop table tb

/*
dt                                      a1                   a2                   a3                   
--------------------------------------- -------------------- -------------------- -------------------- 
2010-11-15 10:00:00~2010-11-15 11:00:00 4.00                 6.00                 5.00
2010-11-15 11:00:00~2010-11-15 12:00:00 9.00                 5.60                 6.40
2010-11-15 12:00:00~2010-11-15 13:00:00 11.00                5.33                 4.00

（所影响的行数为 3 行）
*/