如何列按小时，行按分钟的交叉统计啊。！急！
表结构
测点号(cdh) 时间(rectime) 采集值(val)
202007-04-24 15:20:11.0001.27
202007-04-24 15:20:11.0001.3200000000000001
202007-04-24 15:20:11.0001.3700000000000001
202007-04-24 15:20:11.0001.4199999999999999
202007-04-24 15:20:11.0001.47
202007-04-24 15:20:11.0001.52
202007-04-24 15:20:11.0001.5700000000000001
202007-04-24 15:20:11.0001.6200000000000001
202007-04-24 15:20:41.0001.03
202007-04-24 15:20:41.0001.0800000000000001
202007-04-24 15:20:41.0001.1299999999999999
202007-04-24 15:20:41.0001.1799999999999999
202007-04-24 15:20:41.0001.23
202007-04-24 15:20:41.0001.28
202007-04-24 15:20:41.0001.3300000000000001
202007-04-24 15:20:41.0001.3799999999999999
想统计出来
小时 ５分钟 １０分钟 １５分钟 ２０分钟 ２５分钟．．．到６０
００
01
02
03
04
05
06
07
08
...
23
第一列表示小时，以后的列表示每５分钟求一个平均值！
我始终做不出来!用ＣＡＳＥ ＷＨＥＮ 但求出来每个小时都有多行！求高手！
[解决办法]
给你顶吧
[解决办法]
declare @t table(cdh int,rectime datetime,val float)
insert into @t select
20, '2007-04-24 15:20:11.000 ',1.27 union all select
20, '2007-04-24 15:20:11.000 ',1.3200000000000001 union all select
20, '2007-04-24 15:20:11.000 ',1.3700000000000001 union all select
20, '2007-04-24 15:20:11.000 ',1.4199999999999999 union all select
20, '2007-04-24 15:20:11.000 ',1.47 union all select
20, '2007-04-24 15:20:11.000 ',1.52 union all select
20, '2007-04-24 15:20:11.000 ',1.5700000000000001 union all select
20, '2007-04-24 15:20:11.000 ',1.6200000000000001 union all select
20, '2007-04-24 15:20:41.000 ',1.03 union all select
20, '2007-04-24 15:20:41.000 ',1.0800000000000001 union all select
20, '2007-04-24 15:20:41.000 ',1.1299999999999999 union all select
20, '2007-04-24 15:20:41.000 ',1.1799999999999999 union all select
20, '2007-04-24 15:20:41.000 ',1.23 union all select
20, '2007-04-24 15:20:41.000 ',1.28 union all select
20, '2007-04-24 15:20:41.000 ',1.3300000000000001 union all select
20, '2007-04-24 15:20:41.000 ',1.3799999999999999
--select * from @t
select datepart(hh,rectime) 小时,
avg(case when datepart(mi,rectime) <=5 then val else 0 end) [5分钟],
avg(case when datepart(mi,rectime) <=10 then val else 0 end) [10分钟],
avg(case when datepart(mi,rectime) <=15 then val else 0 end) [15分钟],
avg(case when datepart(mi,rectime) <=20 then val else 0 end) [20分钟],
avg(case when datepart(mi,rectime) <=25 then val else 0 end) [25分钟],
avg(case when datepart(mi,rectime) <=30 then val else 0 end) [30分钟],
avg(case when datepart(mi,rectime) <=35 then val else 0 end) [35分钟],
avg(case when datepart(mi,rectime) <=40 then val else 0 end) [40分钟],
avg(case when datepart(mi,rectime) <=45 then val else 0 end) [45分钟],
avg(case when datepart(mi,rectime) <=50 then val else 0 end) [50分钟],
avg(case when datepart(mi,rectime) <=55 then val else 0 end) [55分钟],
avg(case when datepart(mi,rectime) <=60 then val else 0 end) [60分钟]
from @t
group by datepart(hh,rectime)
