求字定义数据的和!如何处理?比较复杂

[zoulipeng]

Type a b
A 2000-1-1 1
A 2000-1-2 0
A 2000-1-3 2
A 2000-1-4 3
A 2000-1-5 1
A 2000-1-6 2
A 2000-1-7 0
A 2000-1-8 3
A 2000-1-9 0
A 2000-1-10 1
A 2000-1-11 6
A 2000-1-12 2
.... .....

求类型为A 的6 天统计值
中间结果 B
Type a sum_b
A 2000-1-1 9=(1+0+2+3+1+2)
A 2000-1-2 8=(0+2+3+1+2+0)
A 2000-1-3 11 类推
A 2000-1-4 9
A 2000-1-5 7
A 2000-1-6 12
A 2000-1-7 12
A 2000-1-8 NULL
A 2000-1-9 NULL
A 2000-1-10 NULL
A 2000-1-11 NULL
A 2000-1-12 NULL
.... .....
注意不够6个值统计的为NULL,不计算

然后计算表 B 最小值,及相关时间!
最后结果:
Type a sum_b
A 2000-1-5 7



如何处理?

[子陌红尘]

1、中间结果:
-----------------------------------------
select
c.Type,
c.a,
d.b_sum
from
tabname c,
(select
a.Type,
a.a,
b_sum = sum(b.b)
from
tabname a,
tabname b
where
a.Type = b.Type and (datediff(day,a.a,b.a) between 0 and 5)
group by
a.Type,a.a
having count(*) = 6) d

[子陌红尘]

select
c.Type,
c.a,
d.b_sum
into #t
from
tabname c,
(select
a.Type,
a.a,
b_sum = sum(b.b)
from
tabname a,
tabname b
where
a.Type = b.Type and a.type = 'A' and (datediff(day,a.a,b.a) between 0 and 5)
group by
a.Type,a.a
having count(*) = 6) d


select
a.*
from
#t a
where
not exsits(select 1 from #t where type = a.type and a <> a.a and b_sum < a.bsum)

[zjcxc]

--示例

--示例数据
create table tb(Type varchar(10),a datetime,b int)
insert tb select 'A','2000-1-1' ,1
union all select 'A','2000-1-2' ,0
union all select 'A','2000-1-3' ,2
union all select 'A','2000-1-4' ,3
union all select 'A','2000-1-5' ,1
union all select 'A','2000-1-6' ,2
union all select 'A','2000-1-7' ,0
union all select 'A','2000-1-8' ,3
union all select 'A','2000-1-9' ,0
union all select 'A','2000-1-10',1
union all select 'A','2000-1-11',6
union all select 'A','2000-1-12',2
go

--查询
select top 1 *
from(
select Type,a,b=(
select sum(b) from(
select top 6 b from tb
where Type=a.Type
and a>=a.a
)aa having count(*)=6)
from tb a
where Type='A'
)aa where b is not null
order by b
go

--删除测试
drop table tb

/*--结果

Type a b
---------- --------------------------- ---
A 2000-01-05 00:00:00.000 7

（所影响的行数为 1 行）
--*/