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
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
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)
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 行)
--*/