一个高难度的MS SQL问题请教各位高手！

Question

不可能用一句SQL语句写出来，
如果利用存储过程还有点戏

Answer 1

对了，如果你能确认每个类别的最大数据量也可以用一句SQL解出来，但占用资源太多了。

Answer 2

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
declare @quant int,@count int
declare @i int
declare @sql1 nvarchar(4000),@sql2 nvarchar(4000),@sql3 nvarchar(4000),@sql4 nvarchar(4000)
select @quant=sum(Quantity)*0.2,@count=count(*) from t where type=@type--group by type
set @i=1
set @sql1='set @id1='''' select top 1 @id1=''''''''+cast(a.no as varchar(10))'
set @sql2=' from t a'
set @sql3=' where 1=1'
set @sql4=' and a.Quantity'
set @id=''
--print @quant
select @id=''''+cast([no] as varchar(10))+'''' from t where Quantity=@quant and type=@type
print @id while @id='' and @i<@count
begin
set @sql1=@sql1+'+'''''',''''''+cast('+char(ascii('a')+@i)+'.no as varchar(10))+'''''''''
set @sql2=@sql2+',t '+char(ascii('a')+1)
set @sql3=@sql3+' and '+char(ascii('a')+@i-1)+'.no<'+char(ascii('a')+@i)+'.no '+' and '+char(ascii('a')+@i-1)+'.type='+char(ascii('a')+@i)+'.type '    
set @sql4=@sql4+'+'+char(ascii('a')+@i)+'.Quantity'
set @sql1=@sql1+@sql2+@sql3+@sql4+'='+cast(@Quant as varchar(10))+' and a.type='''+@type+''''
--print @sql1
exec sp_executesql @sql1,N'@id1 varchar(200) output',@id output
if @id<>''
break
else
set @i=@i+1
end
end
-------------------------------------------
declare @type varchar(10)
declare @id varchar(200)
set @type='B'
exec getid @type,@id output
exec('select * from t where no in ('+@id+')')
-------------------------------------------------
No          Type       Quantity    
----------- ---------- ----------- 
11          B          37
13          B          28

Answer 3

改一下：
create proc getid(@type varchar(10),@id varchar(200) output)
as 
begin
declare @quant int,@count int
declare @i int
declare @sql1 nvarchar(4000),@sql2 nvarchar(4000),@sql3 nvarchar(4000),@sql4 nvarchar(4000)
select @quant=sum(Quantity)*0.2,@count=count(*) from t where type=@type--group by type
set @i=1
set @sql1='set @id1='''' select top 1 @id1=''''''''+cast(a.no as varchar(10))+'''''''''
set @sql2=' from t a'
set @sql3=' where 1=1'
set @sql4=' and a.Quantity'
set @id=''
--print @quant
select @id=''''+cast([no] as varchar(10))+'''' from t where Quantity=@quant and type=@type
print @id while @id='' and @i<@count
begin
set @sql1=@sql1+'+'',''+''''''''+cast('+char(ascii('a')+@i)+'.no as varchar(10))+'''''''''
set @sql2=@sql2+',t '+char(ascii('a')+1)
set @sql3=@sql3+' and '+char(ascii('a')+@i-1)+'.no<'+char(ascii('a')+@i)+'.no '+' and '+char(ascii('a')+@i-1)+'.type='+char(ascii('a')+@i)+'.type '    
set @sql4=@sql4+'+'+char(ascii('a')+@i)+'.Quantity'
set @sql1=@sql1+@sql2+@sql3+@sql4+'='+cast(@Quant as varchar(10))+' and a.type='''+@type+''''
print @sql1
exec sp_executesql @sql1,N'@id1 varchar(200) output',@id output
if @id<>''
break
else
set @i=@i+1
end
end

Answer 4

上面还有错。
create proc getid(@type varchar(10),@id varchar(200) output)
as 
begin
declare @quant int,@count int
declare @i int
declare @sql1 nvarchar(4000),@sql2 nvarchar(4000),@sql3 nvarchar(4000),@sql4 nvarchar(4000)
select @quant=sum(Quantity)*0.2,@count=count(*) from t where type=@type--group by type
set @i=1
set @sql1='set @id1='''' select top 1 @id1=''''''''+cast(a.no as varchar(10))+'''''''''
set @sql2=' from t a'
set @sql3=' where 1=1'
set @sql4=' and a.Quantity'
set @id=''
--print @quant
select @id=''''+cast([no] as varchar(10))+'''' from t where Quantity=@quant and type=@type
print @id while @id='' and @i<@count
begin
set @sql1=@sql1+'+'',''+''''''''+cast('+char(ascii('a')+@i)+'.no as varchar(10))+'''''''''
set @sql2=@sql2+',t '+char(ascii('a')+1)
set @sql3=@sql3+' and '+char(ascii('a')+@i-1)+'.no<'+char(ascii('a')+@i)+'.no '+' and '+char(ascii('a')+@i-1)+'.type='+char(ascii('a')+@i)+'.type '    
set @sql4=@sql4+'+'+char(ascii('a')+@i)+'.Quantity'
set @sql1=@sql1+@sql2+@sql3+@sql4+'='+cast(@Quant as varchar(10))+' and a.type='''+@type+''''
print @sql1
exec sp_executesql @sql1,N'@id1 varchar(200) output',@id output
if @id<>''
                  begin
break
                  end
else
                  begin
                      set @sql2=' from t a'
                      set @sql3=' where 1=1'
                      set @sql4=' and a.Quantity'
                      set @id=''
set @i=@i+1
                  end
end
end

Answer 5

to j9988(j9988) 
存储过程好像还是有点问题，
当@type参数为'b'时输出的结果如下：set @id1='' select top 1 @id1=''''+cast(a.no as varchar(10))+''''+','+''''+cast(b.no as varchar(10))+'''' from t a,t b where 1=1 and a.no<b.no  and a.type=b.type  and a.Quantity+b.Quantity=65 and a.type='b'

Answer 6

我也不知道当时怎么写的这程序！重写：create proc getid(@type varchar(10),@id varchar(200) output)
as 
begin
set nocount on declare @quant int,@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<@quantif 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
)
begin
print '1'
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
endinsert @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<=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])
enddeclare @type varchar(10)
declare @id varchar(200)
set @type='b'
exec getid @type,@id output
exec('select * from t where charindex('',''+right(''000''+cast(no as varchar(10)),4)+'','','',''+'''+@id+'''+'','')>0')

Answer 7

测试：
declare @type varchar(10)
declare @id varchar(200)
set @type='a'
exec getid @type,@id output
exec('select * from t where charindex('',''+right(''000''+cast(no as varchar(10)),4)+'','','',''+'''+@id+'''+'','')>0')
set @type='b'
exec getid @type,@id output
exec('select * from t where charindex('',''+right(''000''+cast(no as varchar(10)),4)+'','','',''+'''+@id+'''+'','')>0')No          Type       Quantity    
----------- ---------- ----------- 
2           A          20No          Type       Quantity    
----------- ---------- ----------- 
7           B          11
18          B          12

Answer 8

语句这样就可以了！
declare @type varchar(10)
declare @id varchar(200)
set @type='a'
exec getid @type,@id outputselect * from t where charindex(','+right('000'+cast(no as varchar(10)),4)+',',','+@id+',')>0

Answer 9

create proc getid(@type varchar(10),@id varchar(200) output)
as 
begin
set nocount on declare @quant int,@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<@quantif 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
)
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
end
语句这样就可以了！
declare @type varchar(10)
declare @id varchar(200)
set @type='a'
exec getid @type,@id outputselect * from t where charindex(','+right('000'+cast(no as varchar(10)),4)+',',','+@id+',')>0

Answer 10

上面少粘贴了一断。
create proc getid(@type varchar(10),@id varchar(200) output)
as 
begin
set nocount on declare @quant int,@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<@quantif 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
)
begin
print '1'
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
endinsert @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<=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='a'
exec getid @type,@id outputselect * from t where charindex(','+right('000'+cast(no as varchar(10)),4)+',',','+@id+',')>0

Answer 11

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   ,'B',    1
--union all select 22   ,'B',    1    
union all select 23   ,'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<@quantif 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
)
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
endinsert @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])
enddeclare @type varchar(10)
declare @id varchar(200)
set @type='b'
exec getid @type,@id outputselect * from t where charindex(','+right('000'+cast(no as varchar(10)),4)+',',','+@id+',')>0No          Type       Quantity    
----------- ---------- ----------- 
2           A          20（所影响的行数为 1 行）
No          Type       Quantity    
----------- ---------- ----------- 
11          B          37
12          B          29

Answer 12

昨晚喝晕了！SORRY！
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   ,'B',    1
--union all select 22   ,'B',    1    
union all select 23   ,'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<@quantif 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
)
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
endinsert @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 outputselect * from t where charindex(','+right('000'+cast(no as varchar(10)),4)+',',','+@id+',')>0No          Type       Quantity    
----------- ---------- ----------- 
2           A          20（所影响的行数为 1 行）
No          Type       Quantity    
----------- ---------- ----------- 
11          B          37
12          B          29

Answer 13

一天进不来!上面错把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)
endinsert @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

Answer 14

数据如：
No          Type       Quantity    
----------- ---------- ----------- 
21          C          21
22          C          1
23          C          2
24          C          1
25          C          2
26          C          1
27          C          3
28          C          1
29          C          3
合计　　合计＊0.2　　　
-----　 -------- 
35        7.0答案其实有三组：（22.27.29）和（23.25.27）（23.25.29）程序中只取一组No      Type       Quantity    
----- ---------- ----------- 
22        C          1
27        C          3
29        C          3

Answer 15

Toj9988:高手！·一定向你学习！！·