数据如下: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条记录。
求高手解决?
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条记录。
求高手解决?
解决方案 »
- 请高手指点
- 求删除数据库中所有表的SQL语句
- sqlsever 中的登陆用户,数据库用户和角色的问题!
- 比较头痛的一个查询语句,请指导!
- 急!急!急链接服器提示未建立相关链接
- 请教各位高手关于一段更新语句的写法
- 我在win98下能安装sql2000的客户端么??能安装什么版本的客户端??
- 请问在小组开发时使用的SQL数据库,是放在sourcesafe中好还是共享一个服务器上的数据库好?
- db2 能否返回一个result sets ?
- 请教各位,使用 ADO 时,插入#import "...msado15.dll" 出错(VC6.0)
- Sqlserver数据库表备注查件,各位大侠知道吗?
- 新手.请问一下SQL server 触发器里使用execute时,不能删除和修改表里的数据..请各路大虾打救..
---------创建测试数据---------
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
*/
把上面的查询列显示写出来就好。--查询
;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