一个高难度的MS SQL问题请教各位高手！
现有表test结构如下
No Type Quantity
1 A 21
2 A 20
3 A 13
4 A 15
5 A 14
6 A 17
7 B 11
8 B 21
9 B 26
10 B 22
11 B 37
12 B 29
13 B 28
14 B 16
15 B 36
16 B 25
17 B 13
18 B 12
19 B 20
20 B 31
21 C 21
......
......
要求：
从每个相同类别（Type）的数据中取出几个序号（个数不固定），取出来的序号满足以下几个条件：
1、取出来的序号对应Quantity的和大于或等于此类别所有序号对应Quantity的和的5/1 （即sub_sum(quantity)>=total_sum(quantity)*0.2）
2、没有任何其他的序号组合对应Quantity的和比所取出序号对应Quantity的和（即sub_sum(quantity)）更小且满足条件1
3、取出来的序号个数最少
如上表数据，
A类取出的最终结果应该是序号2
B类取出的最终结果应该是序号11,12
---------------------------------------------------------------
一天进不来!上面错把WHERE写成IF了.
-------建表-------
create table t(No int, Type varchar(10), Quantity
int)
insert t select 1, 'A', 21
union all select 2 ,'A', 20
union all select 3 ,'A', 13
union all select 4 ,'A', 15
union all select 5 ,'A', 14
union all select 6 ,'A', 17
union all select 7 ,'B', 11
union all select 8 ,'B', 21
union all select 9 ,'B', 26
union all select 10 ,'B', 22
union all select 11 ,'B', 37
union all select 12 ,'B', 29
union all select 13 ,'B', 28
union all select 14 ,'B', 16
union all select 15 ,'B', 36
union all select 16 ,'B', 25
union all select 17 ,'B', 13
union all select 18 ,'B', 12
union all select 19 ,'B', 20
union all select 20 ,'B', 31
union all select 21 ,'C', 21
-------过程-------
create proc getid(@type varchar(10),@id varchar(200) output)
as
begin
set nocount on
declare @quant decimal(10,2),@count int
select @quant=sum(Quantity)*0.2,@count=count(*) from t where type=@type
declare @a table (NO varchar(200),Quantity int)
declare @b table (NO varchar(200),Quantity int)
insert @a select top 1 right('000'+cast(NO as varchar(10)),4),Quantity
from t
where type=@type and Quantity>=@quant
order by Quantity
insert @b select right('000'+cast(NO as varchar(10)),4),Quantity
from t
where type=@type
and Quantity<@quant
while exists
(
select 1 from @b a,t b
where b.type=@type
and right(a.no,4)<right('000'+cast(b.NO as varchar(10)),4)
and a.Quantity+b.Quantity<@quant
and a.no+','+right('000'+cast(b.NO as varchar(10)),4)
not in
(select no from @b)
)
begin
insert @b select a.NO+','+right('000'+cast(b.NO as varchar(10)),4)
,a.Quantity+b.Quantity
from @b a,t b
where right(a.no,4)<right('000'+cast(b.NO as varchar(10)),4)
and b.type=@type
and a.Quantity+b.Quantity<@quant
and a.no+','+right('000'+cast(b.NO as varchar(10)),4) not in
(select no from @b)
end
insert @a select a.NO+','+right('000'+cast(b.NO as varchar(10)),4)
,a.Quantity+b.Quantity from @b a,t b
where right(a.no,4)<right('000'+cast(b.NO as varchar(10)),4)
and b.type=@type
and a.Quantity+b.Quantity>=@quant
and a.Quantity+b.Quantity<=isnull(
(select top 1 Quantity from @a order by Quantity)
,@quant*5)
set @id=''
select top 1 @id=[NO] from @a order by
quantity,len([NO])
end
-------语句调用-------
declare @type varchar(10)
declare @id varchar(200)
set @type='b'
exec getid @type,@id output
select * from t where
charindex(','+right('000'+cast(no as varchar(10)),4)+',',','+@id+',')>0
