假设有表
A B C D
6 2011-07-15 00:00:00.000 0.7390 8.4300
6 2011-07-14 00:00:00.000 0.4548 8.5800
6 2011-07-13 00:00:00.000 0.4408 8.6100
6 2011-07-12 00:00:00.000 0.6421 8.5000
6 2011-07-11 00:00:00.000 0.4410 8.7500
6 2011-07-08 00:00:00.000 0.5267 8.8200
6 2011-07-07 00:00:00.000 0.8746 8.7700
6 2011-07-06 00:00:00.000 0.6297 8.8200
6 2011-07-05 00:00:00.000 1.5191 8.8400
6 2011-07-04 00:00:00.000 0.8601 8.7100
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
求C的和为<=100的时候 D的平均值
A B C D
6 2011-07-15 00:00:00.000 0.7390 8.4300
6 2011-07-14 00:00:00.000 0.4548 8.5800
6 2011-07-13 00:00:00.000 0.4408 8.6100
6 2011-07-12 00:00:00.000 0.6421 8.5000
6 2011-07-11 00:00:00.000 0.4410 8.7500
6 2011-07-08 00:00:00.000 0.5267 8.8200
6 2011-07-07 00:00:00.000 0.8746 8.7700
6 2011-07-06 00:00:00.000 0.6297 8.8200
6 2011-07-05 00:00:00.000 1.5191 8.8400
6 2011-07-04 00:00:00.000 0.8601 8.7100
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
求C的和为<=100的时候 D的平均值
select avg(d) d
from tb t
where (select sum(c) from tb where b >= t.b) <= 100 -- b >= t.b 日期按倒序算,反之升序。
select avg(d) d
from tb t
where (select sum(c) from tb where a = t.a and b >= t.b) <= 100 -- b >= t.b 日期按倒序算,反之升序。
group by A
having sum(C)<=100
avg(d) d
from
tb t
where
(select sum(c) from tb where b >= t.b) <= 100
create table #tb
(A int, B datetime, C float, D float)
insert #tb
select 6 ,'2011-07-15 00:00:00.000', 10, 8.4300 union all
select 6 ,'2011-07-14 00:00:00.000', 5, 8.5800 union all
select 6 ,'2011-07-13 00:00:00.000', 20, 8.6100 union all
select 6 ,'2011-07-12 00:00:00.000', 35, 8.5000 union all
select 6 ,'2011-07-11 00:00:00.000', 12, 8.7500 union all
select 6 ,'2011-07-08 00:00:00.000', 14, 8.8200 union all
select 6 ,'2011-07-07 00:00:00.000', 18, 8.7700 union all
select 6 ,'2011-07-06 00:00:00.000', 60, 8.8200 union all
select 6 ,'2011-07-05 00:00:00.000', 40, 8.8400 union all
select 6 ,'2011-07-04 00:00:00.000', 9, 8.7100 union allselect 7 ,'2011-07-15 00:00:00.000', 11, 8 union all
select 7 ,'2011-07-14 00:00:00.000', 5, 8 union all
select 7 ,'2011-07-13 00:00:00.000', 20, 16 union all
select 7 ,'2011-07-12 00:00:00.000', 35, 12 union all
select 7 ,'2011-07-11 00:00:00.000', 12, 18 union all
select 7 ,'2011-07-08 00:00:00.000', 14, 30 union all
select 7 ,'2011-07-07 00:00:00.000', 18, 8.7700 union all
select 7 ,'2011-07-06 00:00:00.000', 60, 8.8200 union all
select 7 ,'2011-07-05 00:00:00.000', 40, 8.8400 union all
select 7 ,'2011-07-04 00:00:00.000', 9, 8.7100
;with TempA as (select Row_number()over(partition by A order by B desc) as num ,* from #tb)
,TempB as (select A,num from TempA as a1 where
(select sum(a2.C) from TempA as a2 where a1.A=a2.A and a2.num<=a1.num)<=100
and (select sum(a2.C) from TempA as a2 where a1.A=a2.A and a2.num<=a1.num+1)>100)
select a.A,avg(a.D) as [avg] from TempA as a join TempB as b on b.A=a.A and a.num<=b.num group by a.A搞得有点复杂。。哈哈。。
create table #tb
(A int, B datetime, C float, D float)
insert #tb
select 6 ,'2011-07-15 00:00:00.000', 10, 8.4300 union all
select 6 ,'2011-07-14 00:00:00.000', 5, 8.5800 union all
select 6 ,'2011-07-13 00:00:00.000', 20, 8.6100 union all
select 6 ,'2011-07-12 00:00:00.000', 35, 8.5000 union all
select 6 ,'2011-07-11 00:00:00.000', 12, 8.7500 union all
select 6 ,'2011-07-08 00:00:00.000', 14, 8.8200 union all
select 6 ,'2011-07-07 00:00:00.000', 18, 8.7700 union all
select 6 ,'2011-07-06 00:00:00.000', 60, 8.8200 union all
select 6 ,'2011-07-05 00:00:00.000', 40, 8.8400 union all
select 6 ,'2011-07-04 00:00:00.000', 9, 8.7100 union allselect 7 ,'2011-07-15 00:00:00.000', 11, 8 union all
select 7 ,'2011-07-14 00:00:00.000', 5, 8 union all
select 7 ,'2011-07-13 00:00:00.000', 20, 16 union all
select 7 ,'2011-07-12 00:00:00.000', 35, 12 union all
select 7 ,'2011-07-11 00:00:00.000', 12, 18 union all
select 7 ,'2011-07-08 00:00:00.000', 14, 30 union all
select 7 ,'2011-07-07 00:00:00.000', 18, 8.7700 union all
select 7 ,'2011-07-06 00:00:00.000', 60, 8.8200 union all
select 7 ,'2011-07-05 00:00:00.000', 40, 8.8400 union all
select 7 ,'2011-07-04 00:00:00.000', 9, 8.7100select A,avg(D) as [avg] from #tb as t1 where
(select sum(t2.c) from #tb as t2 where t1.A=t2.A and t2.B>=t1.B)<=100
group by A哈。。原来这样就好了。。9楼绕也几个圈。。真失败。。
又学到东西了..