这是表数据,目前是12个,我想得到6条结果,具体就是 bh的为数为2 和4 的求平均,6和8的求平均
zb6 sj bh
1.426000 2006-05-07 01:00:00.000 1-2
1.420300 2006-05-07 03:00:00.000 1-4
1.420900 2006-05-07 05:00:00.000 1-6
1.434300 2006-05-07 07:00:00.000 1-8
1.409000 2006-05-07 09:00:00.000 2-2
1.444300 2006-05-07 11:00:00.000 2-4
1.451500 2006-05-07 13:00:00.000 2-6
1.427500 2006-05-07 15:00:00.000 2-8
1.376900 2006-05-07 17:00:00.000 3-2
1.405600 2006-05-07 19:00:00.000 3-4
1.434300 2006-05-07 21:00:00.000 3-6
1.421900 2006-05-07 23:00:00.000 3-8怎么转化为下列结果表 zb6 sj bh
1-2 和1-4的平均 2006-05-07 01:00:00.000 1-4
1-6 和1-8的平均 2006-05-07 07:00:00.000 1-8
2-2 和2-4的平均 2006-05-07 11:00:00.000 2-4
2-6 和2-8的平均 2006-05-07 15:00:00.000 2-8
3-2 和3-4的平均 2006-05-07 19:00:00.000 3-4
3-6 和3-8的平均 2006-05-07 23:00:00.000 3-8
zb6 sj bh
1.426000 2006-05-07 01:00:00.000 1-2
1.420300 2006-05-07 03:00:00.000 1-4
1.420900 2006-05-07 05:00:00.000 1-6
1.434300 2006-05-07 07:00:00.000 1-8
1.409000 2006-05-07 09:00:00.000 2-2
1.444300 2006-05-07 11:00:00.000 2-4
1.451500 2006-05-07 13:00:00.000 2-6
1.427500 2006-05-07 15:00:00.000 2-8
1.376900 2006-05-07 17:00:00.000 3-2
1.405600 2006-05-07 19:00:00.000 3-4
1.434300 2006-05-07 21:00:00.000 3-6
1.421900 2006-05-07 23:00:00.000 3-8怎么转化为下列结果表 zb6 sj bh
1-2 和1-4的平均 2006-05-07 01:00:00.000 1-4
1-6 和1-8的平均 2006-05-07 07:00:00.000 1-8
2-2 和2-4的平均 2006-05-07 11:00:00.000 2-4
2-6 和2-8的平均 2006-05-07 15:00:00.000 2-8
3-2 和3-4的平均 2006-05-07 19:00:00.000 3-4
3-6 和3-8的平均 2006-05-07 23:00:00.000 3-8
insert into @t select 1.426000,'2006-05-07 01:00:00.000','1-2'
insert into @t select 1.420300,'2006-05-07 03:00:00.000','1-4'
insert into @t select 1.420900,'2006-05-07 05:00:00.000','1-6'
insert into @t select 1.434300,'2006-05-07 07:00:00.000','1-8'
insert into @t select 1.409000,'2006-05-07 09:00:00.000','2-2'
insert into @t select 1.444300,'2006-05-07 11:00:00.000','2-4'
insert into @t select 1.451500,'2006-05-07 13:00:00.000','2-6'
insert into @t select 1.427500,'2006-05-07 15:00:00.000','2-8'
insert into @t select 1.376900,'2006-05-07 17:00:00.000','3-2'
insert into @t select 1.405600,'2006-05-07 19:00:00.000','3-4'
insert into @t select 1.434300,'2006-05-07 21:00:00.000','3-6'
insert into @t select 1.421900,'2006-05-07 23:00:00.000','3-8'
select
(a.zb6+b.zb6)/2 as zb6,b.sj,b.bh
from
@t a,@t b
where
left(a.bh, 1)=left(b.bh,1)
and
right(a.bh,1)=right(b.bh,1)-2
and
right(a.bh,1)%4=2/*
zb6 sj bh
------------- ------------------------------------------------------ ----
1.42315000 2006-05-07 03:00:00.000 1-4
1.42760000 2006-05-07 07:00:00.000 1-8
1.42665000 2006-05-07 11:00:00.000 2-4
1.43950000 2006-05-07 15:00:00.000 2-8
1.39125000 2006-05-07 19:00:00.000 3-4
1.42810000 2006-05-07 23:00:00.000 3-8
*/
group by case when bh like '%[24]' then 1 when bh like '%[68]' then 2 end
insert into t select 1.426000,'2006-05-07 01:00:00.000','1-2'
insert into t select 1.420300,'2006-05-07 03:00:00.000','1-4'
insert into t select 1.420900,'2006-05-07 05:00:00.000','1-6'
insert into t select 1.434300,'2006-05-07 07:00:00.000','1-8'
insert into t select 1.409000,'2006-05-07 09:00:00.000','2-2'
insert into t select 1.444300,'2006-05-07 11:00:00.000','2-4'
insert into t select 1.451500,'2006-05-07 13:00:00.000','2-6'
insert into t select 1.427500,'2006-05-07 15:00:00.000','2-8'
insert into t select 1.376900,'2006-05-07 17:00:00.000','3-2'
insert into t select 1.405600,'2006-05-07 19:00:00.000','3-4'
insert into t select 1.434300,'2006-05-07 21:00:00.000','3-6'
insert into t select 1.421900,'2006-05-07 23:00:00.000','3-8'
gocreate view v1 as
select
(a.zb6+b.zb6)/2 as zb6,b.sj,b.bh
from
t a,t b
where
left(a.bh, 1)=left(b.bh,1)
and
right(a.bh,1)=right(b.bh,1)-2
and
right(a.bh,1)%4=2
goselect * from v1
/*
zb6 sj bh
------------- ------------------------------------------------------ ----
1.42315000 2006-05-07 03:00:00.000 1-4
1.42760000 2006-05-07 07:00:00.000 1-8
1.42665000 2006-05-07 11:00:00.000 2-4
1.43950000 2006-05-07 15:00:00.000 2-8
1.39125000 2006-05-07 19:00:00.000 3-4
1.42810000 2006-05-07 23:00:00.000 3-8
*/
go
drop view v1
drop table t
go
insert into t select 1.426000,'2006-05-07 01:00:00.000','1-2'
insert into t select 1.420300,'2006-05-07 03:00:00.000','1-4'
insert into t select 1.420900,'2006-05-07 05:00:00.000','1-6'
insert into t select 1.434300,'2006-05-07 07:00:00.000','1-8'
insert into t select 1.409000,'2006-05-07 09:00:00.000','2-2'
insert into t select 1.444300,'2006-05-07 11:00:00.000','2-4'
insert into t select 1.451500,'2006-05-07 13:00:00.000','2-6'
insert into t select 1.427500,'2006-05-07 15:00:00.000','2-8'
insert into t select 1.376900,'2006-05-07 17:00:00.000','3-2'
insert into t select 1.405600,'2006-05-07 19:00:00.000','3-4'
insert into t select 1.434300,'2006-05-07 21:00:00.000','3-6'
insert into t select 1.421900,'2006-05-07 23:00:00.000','3-8'
go
select avg(zb6) zb6,max(sj) sj,max(bh) bh from t
group by left(bh,1),case when bh like '%[24]' then 1 when bh like '%[68]' then 2 end
go
drop table t
select 1.421900,'2006-05-07 23:00:00.000','3-8'