一个比较困难的sql查询，请高手看看

[policy1234]

数据如下：

ID AA BB CC
1 10-1 BB1 17.182
2 10-1 BB1 16.368
3 10-1 BB1 8.557
4 10-1 BB1 8.22
5 10-1 BB1 6.74
6 10-1 BB1 6.478
7 10-1 BB1 5.499
8 10-1 BB1 5.35
9 10-1 BB1 5.278
10 10-2 BB1 18.169
11 10-2 BB1 11.915
12 10-2 BB1 9.181
13 10-2 BB1 8.636
14 10-2 BB1 7.728
15 10-2 BB1 7.01
16 10-2 BB1 6.153
17 10-2 BB1 5.755
18 10-2 BB2 8.854
19 10-2 BB2 8.446
20 10-2 BB2 7.951
21 10-2 BB2 6.416
22 10-2 BB2 6.29
23 10-2 BB2 6.263
24 10-2 BB2 5.939
25 10-2 BB2 5.715
26 10-2 BB2 5.535
27 10-2 BB2 5.489
28 10-2 BB2 5.362
29 10-2 BB2 5.317
30 10-2 BB2 5.274
31 10-2 BB2 5.176

需要筛选出如下数据：
ID AA BB CC
1 10-1 BB1 17.182
2 10-1 BB1 16.368
10 10-2 BB1 18.169
11 10-2 BB1 11.915
18 10-2 BB2 8.854
19 10-2 BB2 8.446
20 10-2 BB2 7.951
21 10-2 BB2 6.416
22 10-2 BB2 6.29
规则：
按照AA(日期)和BB(类型)按照CC值降序排列，计算出CC的总和，乘以系数为比较值，取出记录CC前面n个之后大于比较值即可，后面的就不取了。
如：10-1 BB1 CC总和=79.67 ，比较值为=79.67*0.4=31.87,取前面2个记录即可，前面2个记录之后为33.55>31.87,如果去一个17.18<31.87,因此10-1 BB1 应该取2条记录。
10-2 BB1 CC总和=74.55，比较值为=74.55*0.4=29.82，也只能取前2条，前2条之后为30.08>29.82.取前2条。
10-2 BB2 CC总和=88.03，比较值为=88.03*0.4=35.212，如果取前4条CC之和=31.667<35.212,前5条CC之和为37.957>35.212,因此取前5条记录。
求高手解决？

[ChangeMyself2012]

---------创建测试数据---------
if OBJECT_ID('A') is not null drop table A
create TABLE A
(ID INT identity(1,1),AA nvarchar(10),BB nvarchar(10),CC decimal(18,3))
go
insert into A(AA,BB,CC)
select	'10-1','BB1','17.182' union all
select	'10-1','BB1','16.368' union all
select	'10-1','BB1','8.557' union all
select	'10-1','BB1','8.22' union all
select	'10-1','BB1','6.74' union all
select	'10-1','BB1','6.478' union all
select	'10-1','BB1','5.499' union all
select	'10-1','BB1','5.35' union all
select	'10-1','BB1','5.278' union all
select	'10-2','BB1','18.169' union all
select	'10-2','BB1','11.915' union all
select	'10-2','BB1','9.181' union all
select	'10-2','BB1','8.636' union all
select	'10-2','BB1','7.728' union all
select	'10-2','BB1','7.01' union all
select	'10-2','BB1','6.153' union all
select	'10-2','BB1','5.755' union all
select	'10-2','BB2','8.854' union all
select	'10-2','BB2','8.446' union all
select	'10-2','BB2','7.951' union all
select	'10-2','BB2','6.416' union all
select	'10-2','BB2','6.29' union all
select	'10-2','BB2','6.263' union all
select	'10-2','BB2','5.939' union all
select	'10-2','BB2','5.715' union all
select	'10-2','BB2','5.535' union all
select	'10-2','BB2','5.489' union all
select	'10-2','BB2','5.362' union all
select	'10-2','BB2','5.317' union all
select	'10-2','BB2','5.274' union all
select	'10-2','BB2','5.176' 
--查询
;with T as(
select *,(select sum(CC) from A where BB= b.BB and AA=b.AA and ID<=b.ID) AddCC,(select sum(CC) from A where BB= b.BB and AA=b.AA)*0.4 SUMCC
from A  b )
select * from T where AddCC<=SUMCC or CC>AddCC-SUMCC
/*
ID	AA  BB  CC  AddCC  SUMCC
--- --- --- --- --- ---
1	10-1	BB1	17.182	17.182	31.8688
2	10-1	BB1	16.368	33.550	31.8688
10	10-2	BB1	18.169	18.169	29.8188
11	10-2	BB1	11.915	30.084	29.8188
18	10-2	BB2	8.854	8.854	35.2108
19	10-2	BB2	8.446	17.300	35.2108
20	10-2	BB2	7.951	25.251	35.2108
21	10-2	BB2	6.416	31.667	35.2108
22	10-2	BB2	6.290	37.957	35.2108
*/

[ChangeMyself2012]

不好意思，后面两列不用显示！ 把上面的查询列显示写出来就好。

--查询
;with T as(
select *,(select sum(CC) from A where BB= b.BB and AA=b.AA and ID<=b.ID) AddCC,(select sum(CC) from A where BB= b.BB and AA=b.AA)*0.4 SUMCC
from A  b )
select ID,AA,BB,CC from T where AddCC<=SUMCC or CC>AddCC-SUMCC

[还在加载中灬]

引用 前面 ChangeMyself2012 的回复:

...

我觉得“and ID<=b.ID”改为“and CC>=b.CC”可能会好点 另外 如果楼主是使用SQL2005+的话，SUMCC列改用开窗函数效率要好点