126,113,123,109,115 71.10,78.90,50.00,98.50,101.60(包括30~50) 400.10
136,131,119,133,135 89.00,76.60,68.70,81.20,84.60(包括30~50) 400.10
127,124,134,137,125 72.20,69.90,82.30,100.00,75.50(包括30~50) 399.90
110,108,116,128,138 63.50,56.60,56.70,73.30,150.00(如果numjg中无30~50)400.10
136,131,119,133,135 89.00,76.60,68.70,81.20,84.60(包括30~50) 400.10
127,124,134,137,125 72.20,69.90,82.30,100.00,75.50(包括30~50) 399.90
110,108,116,128,138 63.50,56.60,56.70,73.30,150.00(如果numjg中无30~50)400.10
136,131,119,133,135 89.00,76.60,68.70,81.20,84.60(包括30~50一个) 400.10
127,124,134,137,125 72.20,69.90,82.30,100.00,75.50(包括30~50一个) 399.90
110,108,116,128,138 63.50,56.60,56.70,73.30,150.00(如果numjg中无30~50)400.10
这一条很难做到,
因为里面也许有30到50的.但加起来不一定等于400左右.
我中午改吧.
这一条很难做到,
因为里面也许有30到50的.但加起来不一定等于400左右.
-------------------------------------------------------------
如果这样
那就不用管30到50的,30~50的在每一组中优先使用,如果拼不成就不用管他了。
AS
begin
set nocount on
declare @level int--@Scope代表成包码数,@MaxPack代表成包最大件数数
declare @i int,@j intset @MaxPack=(case when @MaxPack<=(select CEILING(1.0*@scope/avg(sj)) from numjg)
then @MaxPack
else (select CEILING(1.0*@scope/avg(sj)) from numjg)
end)
create table #t(id varchar(80),sj varchar(100),value decimal(10,2),level int,flag int,time int)
declare @sql varchar(8000)
declare @sele_id varchar(8000),@sele_sj varchar(8000),@sele_V varchar(8000),@sele_flag varchar(8000)
declare @where_id varchar(8000),@where_sj varchar(8000),@from varchar(8000),@Where_flag varchar(8000)
declare @time int,@len_id int,@flag int
set @time=1
create table #numjg(id1 int identity,id int,sj decimal(10,2))
insert #numjg(id,sj) select id,sj from numjg order by newid()
set @i=@MaxPack
set @j=1
select @sele_id='insert #t select top 1 ',@sele_sj='',@sele_V='',@sele_flag='',@where_id='',@where_sj='',@from=' from'
select @Where_flag='',@flag=0
print @MaxPack
--找有一个在30到50之间的
while @i>=2 and @i<=@MaxPack
begin
while (1=1)
begin
set @j=1
select @sele_id='insert #t select top 1 ',@sele_sj='',@sele_V='',@sele_flag='',@where_id='',@where_sj='',@from=' from'
select @Where_flag=''
while @j<=@i
begin
set @sele_id=@sele_id+'rtrim(A'+rtrim(@j)+'.id)+'',''+'
set @sele_sj=@sele_sj+'rtrim(A'+rtrim(@j)+'.sj)+'',''+'
set @sele_V=@sele_V+'A'+rtrim(@j)+'.sj+'
-- set @sele_flag=@sele_flag+'(case when A'+rtrim(@j)+'.sj between 30 and 50 then 0 else 1 end)*'
set @from=@from+' #numjg A'+rtrim(@j)+','
set @where_id=@where_id+'A'+rtrim(@j)+'.id1<A'+rtrim(@j+1)+'.id1 and '
set @len_id=len('A'+rtrim(@j)+'.id1<A'+rtrim(@j+1)+'.id1 and ')
set @where_sj=@where_sj+'A'+rtrim(@j)+'.sj+'
set @Where_flag=@Where_flag+(case when @flag=0 then '(case when A'+rtrim(@j)+'.sj between 30 and 50 then 1 else 0 end)+' else 'A'+rtrim(@j)+'.sj<30 and A'+rtrim(@j)+'.sj>50' end)
set @j=@j+1
end
set @Where_flag=(case when @flag=0 then left(@Where_flag,len(@Where_flag)-1)+'=1 and ' else @Where_flag end)
set @sql= left(@sele_id,len(@sele_id)-5)+','+left(@sele_sj,len(@sele_sj)-5)+','+left(@sele_V,len(@sele_V)-1)+','+rtrim(@i)+','+'0,'+rtrim(@time)+' '+left(@from,len(@from)-1)+' where '+@Where_flag+left(@where_id,len(@where_id)-@len_id)+left(@where_sj,len(@where_sj)-1)+' between '+rtrim(@Scope-0.3)+' and '+rtrim(@Scope+0.3)
-- print @sql
exec(@sql)
if @@rowcount=0
begin
break
end
delete A from #numjg A,#t B where ','+rtrim(B.id)+',' like '%,'+rtrim(A.id)+',%' and B.time=@time
end
set @i=@i-1
end
--找不存在30到50的
set @flag=1
set @i=@MaxPack
set @j=1
select @sele_id='insert #t select top 1 ',@sele_sj='',@sele_V='',@sele_flag='',@where_id='',@where_sj='',@from=' from'
select @Where_flag=''
while @i>=2 and @i<=@MaxPack
begin
while (1=1)
begin
set @j=1
select @sele_id='insert #t select top 1 ',@sele_sj='',@sele_V='',@sele_flag='',@where_id='',@where_sj='',@from=' from'
select @Where_flag=''
while @j<=@i
begin
set @sele_id=@sele_id+'rtrim(A'+rtrim(@j)+'.id)+'',''+'
set @sele_sj=@sele_sj+'rtrim(A'+rtrim(@j)+'.sj)+'',''+'
set @sele_V=@sele_V+'A'+rtrim(@j)+'.sj+'
-- set @sele_flag=@sele_flag+'(case when A'+rtrim(@j)+'.sj between 30 and 50 then 0 else 1 end)*'
set @from=@from+' #numjg A'+rtrim(@j)+','
set @where_id=@where_id+'A'+rtrim(@j)+'.id1<A'+rtrim(@j+1)+'.id1 and '
set @len_id=len('A'+rtrim(@j)+'.id1<A'+rtrim(@j+1)+'.id1 and ')
set @where_sj=@where_sj+'A'+rtrim(@j)+'.sj+'
set @Where_flag=@Where_flag+(case when @flag=0 then '(case when A'+rtrim(@j)+'.sj between 30 and 50 then 1 else 0 end)+' else '(A'+rtrim(@j)+'.sj<30 or A'+rtrim(@j)+'.sj>50) and ' end)
set @j=@j+1
end
set @Where_flag=(case when @flag=0 then left(@Where_flag,len(@Where_flag)-1)+'=1 and ' else @Where_flag end)
set @sql= left(@sele_id,len(@sele_id)-5)+','+left(@sele_sj,len(@sele_sj)-5)+','+left(@sele_V,len(@sele_V)-1)+','+rtrim(@i)+','+'0,'+rtrim(@time)+' '+left(@from,len(@from)-1)+' where '+@Where_flag+left(@where_id,len(@where_id)-@len_id)+left(@where_sj,len(@where_sj)-1)+' between '+rtrim(@Scope-0.3)+' and '+rtrim(@Scope+0.3)
exec(@sql)
if @@rowcount=0
begin
break
end
delete A from #numjg A,#t B where ','+rtrim(B.id)+',' like '%,'+rtrim(A.id)+',%' and B.time=@time
end
set @i=@i-1
enddrop table #numjgselect id,sj,value from #t
drop table #t
end
这是“爱好”。不是腐败。
我也想腐败,但总是没有机会。
AS
begin
set nocount ondeclare @level int--@Scope代表成包码数,@MaxPack代表成包最大件数数
declare @i int,@j intset @MaxPack=(case when @MaxPack<=(select CEILING(1.0*@scope/avg(sj)) from numjg)
then @MaxPack
else (select CEILING(1.0*@scope/avg(sj)) from numjg)
end)
create table #t(id varchar(80),sj varchar(100),value decimal(10,2),level int,flag int,time int)
declare @sql varchar(8000)
declare @sele_id varchar(8000),@sele_sj varchar(8000),@sele_V varchar(8000),@sele_flag varchar(8000)
declare @where_id varchar(8000),@where_sj varchar(8000),@from varchar(8000),@Where_flag varchar(8000)
declare @time int,@len_id int,@flag int
set @time=1while @time<=3
begincreate table #numjg(id1 int identity,id int,sj decimal(10,2))
insert #numjg(id,sj) select id,sj from numjg order by newid()
set @i=@MaxPack
set @j=1
select @sele_id='insert #t select top 1 ',@sele_sj='',@sele_V='',@sele_flag='',@where_id='',@where_sj='',@from=' from'
select @Where_flag='',@flag=0
--找有一个在30到50之间的
while @i>=2 and @i<=@MaxPack
begin
while (1=1)
begin
set @j=1
select @sele_id='insert #t select top 1 ',@sele_sj='',@sele_V='',@sele_flag='',@where_id='',@where_sj='',@from=' from'
select @Where_flag=''
while @j<=@i
begin
set @sele_id=@sele_id+'rtrim(A'+rtrim(@j)+'.id)+'',''+'
set @sele_sj=@sele_sj+'rtrim(A'+rtrim(@j)+'.sj)+'',''+'
set @sele_V=@sele_V+'A'+rtrim(@j)+'.sj+'
-- set @sele_flag=@sele_flag+'(case when A'+rtrim(@j)+'.sj between 30 and 50 then 0 else 1 end)*'
set @from=@from+' #numjg A'+rtrim(@j)+','
if @j>1
set @where_id=@where_id+'A'+rtrim(@j)+'.id1<A'+rtrim(@j+1)+'.id1 and ' set @len_id=len('A'+rtrim(@j)+'.id1<A'+rtrim(@j+1)+'.id1 and ')
set @where_sj=@where_sj+'A'+rtrim(@j)+'.sj+'
set @Where_flag=@Where_flag+(case when @flag=0 and @j=1 then
'(A'+rtrim(@j)+'.sj between 30 and 50) and ' else '(A'+rtrim(@j)+'.sj<30 or A'+rtrim(@j)+'.sj>50) and ' end)
set @j=@j+1
end
set @sql= left(@sele_id,len(@sele_id)-5)+','+left(@sele_sj,len(@sele_sj)-5)+','+left(@sele_V,len(@sele_V)-1)+','+rtrim(@i)+','+'0,'+rtrim(@time)+' '+left(@from,len(@from)-1)+' where '+@Where_flag+left(@where_id,len(@where_id)-@len_id)+left(@where_sj,len
(@where_sj)-1)+' between '+rtrim(@Scope-0.3)+' and '+rtrim(@Scope+0.3)
exec(@sql)
if @@rowcount=0
begin
break
end
delete A from #numjg A,#t B where ','+rtrim(B.id)+',' like '%,'+rtrim(A.id)+',%' and B.time=@time
end
set @i=@i-1
end
--找不存在30到50的
set @flag=1
set @i=@MaxPack
set @j=1
select @sele_id='insert #t select top 1 ',@sele_sj='',@sele_V='',@sele_flag='',@where_id='',@where_sj='',@from=' from'
select @Where_flag=''
while @i>=2 and @i<=@MaxPack
begin
while (1=1)
begin
set @j=1
select @sele_id='insert #t select top 1 ',@sele_sj='',@sele_V='',@sele_flag='',@where_id='',@where_sj='',@from=' from'
select @Where_flag=''
while @j<=@i
begin
set @sele_id=@sele_id+'rtrim(A'+rtrim(@j)+'.id)+'',''+'
set @sele_sj=@sele_sj+'rtrim(A'+rtrim(@j)+'.sj)+'',''+'
set @sele_V=@sele_V+'A'+rtrim(@j)+'.sj+'
-- set @sele_flag=@sele_flag+'(case when A'+rtrim(@j)+'.sj between 30 and 50 then 0 else 1 end)*'
set @from=@from+' #numjg A'+rtrim(@j)+','
set @where_id=@where_id+'A'+rtrim(@j)+'.id1<A'+rtrim(@j+1)+'.id1 and '
set @len_id=len('A'+rtrim(@j)+'.id1<A'+rtrim(@j+1)+'.id1 and ')
set @where_sj=@where_sj+'A'+rtrim(@j)+'.sj+'
set @Where_flag=@Where_flag+(case when @flag=0 then '(case when A'+rtrim(@j)+'.sj between 30 and 50 then 1 else 0 end)+' else '(A'+rtrim(@j)+'.sj<30 or A'+rtrim(@j)+'.sj>50) and ' end)
set @j=@j+1
end
set @Where_flag=(case when @flag=0 then left(@Where_flag,len(@Where_flag)-1)+'=1 and ' else @Where_flag end)
set @sql= left(@sele_id,len(@sele_id)-5)+','+left(@sele_sj,len(@sele_sj)-5)+','+left(@sele_V,len(@sele_V)-1)+','+rtrim(@i)+','+'0,'+rtrim(@time)+' '+left(@from,len(@from)-1)+' where '+@Where_flag+left(@where_id,len(@where_id)-@len_id)+left(@where_sj,len
(@where_sj)-1)+' between '+rtrim(@Scope-0.3)+' and '+rtrim(@Scope+0.3)
exec(@sql)
if @@rowcount=0
begin
break
end
delete A from #numjg A,#t B where ','+rtrim(B.id)+',' like '%,'+rtrim(A.id)+',%' and B.time=@time
end
set @i=@i-1
enddrop table #numjg
set @time=@time+1
endselect id,sj,value from #t where time=(select top 1 time from #t group by time order by sum(level) desc)drop table #tend
------------------ --------------------------------------- ------------
49,28,2,38,46,8 48.20,69.00,69.60,64.00,84.00,65.00 399.80
6,25,21,13,4,39 35.00,58.00,69.50,59.20,81.00,97.00 399.70
43,47,35,3,22,42 34.00,62.60,64.80,88.00,68.30,82.00 399.70
48,33,23,34,5,30 46.20,64.90,59.00,68.50,65.00,96.30 399.90
1,20,26,40,50,16 36.50,57.00,53.00,95.00,56.10,102.50 400.10
10,37,27,32,36,7 38.50,69.00,51.00,75.50,68.00,98.00 400.00
44,18,19,24,9 36.00,181.40,58.00,62.00,62.30 399.70
29,17,11,14,41 30.60,162.30,67.10,54.30,86.00 400.30