--建表
declare @a table(f1 varchar(10),f2 varchar(10),f3 varchar(10),f4 varchar(10),f5 int,f6 int,f7 int,f8 int)
insert @a select '28729', 'H-1892', 'H-16620', 'S',14, 6, 13,25
insert @a select '28729', 'H-1892', 'H-16621', 'N',3,45,25,8--处理过程:
select identity(int,1,1) as id,* into #t from (
select f1,f2,f3,f4,f5,1 as f from @a
union all
select f1,f2,f3,f4,f6,2 from @a
union all
select f1,f2,f3,f4,f7,3 from @a
union all
select f1,f2,f3,f4,f8,4 from @a
) A order by f1,f2,f3,f4,fselect top 0 0 as N,f1,f2,f3,f4,f5,f into #t1 from #t
declare @num int
set @num=18
declare @i int,@j int,@k int,@l int
select @i=1,@l=@num
while exists(select 1 from #t where f5<>0)
begin
select top 1 @j=f5,@k=id from #t where f5<>0 order by id
if @j>=@l
begin
insert #t1 select @i,f1,f2,f3,f4,@l,f from #t where id=@k
update #t set f5=f5-@l where id=@k
select @i=@i+1,@l=18
end
else
begin
insert #t1 select @i,f1,f2,f3,f4,f5,f from #t where id=@k
update #t set f5=0 where id=@k
set @l=@l-@j
end
endselect
N,f1,f2,f3,f4,
sum(case when f=1 then f5 else 0 end) as TYPE1,
sum(case when f=2 then f5 else 0 end) as TYPE2,
sum(case when f=3 then f5 else 0 end) as TYPE3,
sum(case when f=4 then f5 else 0 end) as TYPE4
from #t1
group by N,f1,f2,f3,f4
order by N
drop table #t1
drop table #tN f1 f2 f3 f4 TYPE1 TYPE2 TYPE3 TYPE4
----------- ---------- ---------- ---------- ---------- ----------- ----------- ----------- -----------
1 28729 H-1892 H-16620 S 14 4 0 0
2 28729 H-1892 H-16620 S 0 2 13 3
3 28729 H-1892 H-16620 S 0 0 0 18
4 28729 H-1892 H-16620 S 0 0 0 4
4 28729 H-1892 H-16621 N 3 11 0 0
5 28729 H-1892 H-16621 N 0 18 0 0
6 28729 H-1892 H-16621 N 0 16 2 0
7 28729 H-1892 H-16621 N 0 0 18 0
8 28729 H-1892 H-16621 N 0 0 5 8(所影响的行数为 9 行)
declare @a table(f1 varchar(10),f2 varchar(10),f3 varchar(10),f4 varchar(10),f5 int,f6 int,f7 int,f8 int)
insert @a select '28729', 'H-1892', 'H-16620', 'S',14, 6, 13,25
insert @a select '28729', 'H-1892', 'H-16621', 'N',3,45,25,8--处理过程:
select identity(int,1,1) as id,* into #t from (
select f1,f2,f3,f4,f5,1 as f from @a
union all
select f1,f2,f3,f4,f6,2 from @a
union all
select f1,f2,f3,f4,f7,3 from @a
union all
select f1,f2,f3,f4,f8,4 from @a
) A order by f1,f2,f3,f4,fselect top 0 0 as N,f1,f2,f3,f4,f5,f into #t1 from #t
declare @num int
set @num=18
declare @i int,@j int,@k int,@l int
select @i=1,@l=@num
while exists(select 1 from #t where f5<>0)
begin
select top 1 @j=f5,@k=id from #t where f5<>0 order by id
if @j>=@l
begin
insert #t1 select @i,f1,f2,f3,f4,@l,f from #t where id=@k
update #t set f5=f5-@l where id=@k
select @i=@i+1,@l=18
end
else
begin
insert #t1 select @i,f1,f2,f3,f4,f5,f from #t where id=@k
update #t set f5=0 where id=@k
set @l=@l-@j
end
endselect
N,f1,f2,f3,f4,
sum(case when f=1 then f5 else 0 end) as TYPE1,
sum(case when f=2 then f5 else 0 end) as TYPE2,
sum(case when f=3 then f5 else 0 end) as TYPE3,
sum(case when f=4 then f5 else 0 end) as TYPE4
from #t1
group by N,f1,f2,f3,f4
order by N
drop table #t1
drop table #tN f1 f2 f3 f4 TYPE1 TYPE2 TYPE3 TYPE4
----------- ---------- ---------- ---------- ---------- ----------- ----------- ----------- -----------
1 28729 H-1892 H-16620 S 14 4 0 0
2 28729 H-1892 H-16620 S 0 2 13 3
3 28729 H-1892 H-16620 S 0 0 0 18
4 28729 H-1892 H-16620 S 0 0 0 4
4 28729 H-1892 H-16621 N 3 11 0 0
5 28729 H-1892 H-16621 N 0 18 0 0
6 28729 H-1892 H-16621 N 0 16 2 0
7 28729 H-1892 H-16621 N 0 0 18 0
8 28729 H-1892 H-16621 N 0 0 5 8(所影响的行数为 9 行)
N f1 f2 f3 f4 TYPE1 TYPE2 TYPE3 TYPE4 1 28729 H-1892 H-16620 S 14 4 0 0
2 28729 H-1892 H-16620 S 0 2 13 3
3 28729 H-1892 H-16620 S 0 0 0 18
4 28729 H-1892 H-16620 S 0 0 0 4
4 28729 H-1892 H-16621 N 3 11 0 0
5 28729 H-1892 H-16621 N 0 18 0 0
6 28729 H-1892 H-16621 N 0 16 2 0
7 28729 H-1892 H-16621 N 0 0 18 0
8 28729 H-1892 H-16621 N 0 0 5 8
select *,type1+type2+type3+type4 as NUM from (
select N,f1,f2,f3,f4,
sum(case when f=1 then f5 else 0 end) as TYPE1,
sum(case when f=2 then f5 else 0 end) as TYPE2,
sum(case when f=3 then f5 else 0 end) as TYPE3,
sum(case when f=4 then f5 else 0 end) as TYPE4
from #t1
group by N,f1,f2,f3,f4
) A
order by N
N f1 f2 f3 f4 TYPE1 TYPE2 TYPE3 TYPE4 NUM 1 28729 H-1892 H-16620 S 14 4 0 0 18
2 28729 H-1892 H-16620 S 0 2 13 3 18
3 28729 H-1892 H-16620 S 0 0 0 18 18
4 28729 H-1892 H-16620 S 0 0 0 4 4
4 28729 H-1892 H-16621 N 3 11 0 0 14
5 28729 H-1892 H-16621 N 0 18 0 0 18
6 28729 H-1892 H-16621 N 0 16 2 0 18
7 28729 H-1892 H-16621 N 0 0 18 0 18
8 28729 H-1892 H-16621 N 0 0 5 8 13
declare @a table(f1 varchar(10),f2 varchar(10),f3 varchar(10),f4 varchar(10),f5 int,f6 int,f7 int,f8 int)
insert @a select '28729', 'H-1892', 'H-16620', 'S',14, 6, 13,25
insert @a select '28729', 'H-1892', 'H-16621', 'N',3,45,25,8--处理过程:
select identity(int,1,1) as id,* into #t from (
select f1,f2,f3,f4,f5,1 as f from @a
union all
select f1,f2,f3,f4,f6,2 from @a
union all
select f1,f2,f3,f4,f7,3 from @a
union all
select f1,f2,f3,f4,f8,4 from @a
) A order by f1,f2,f3,f4,fselect top 0 0 as N,f1,f2,f3,f4,f5,f into #t1 from #t
declare @num int
set @num=18--装箱数量
declare @i int,@j int,@k int,@l int
select @i=1,@l=@num
while exists(select 1 from #t where f5<>0)
begin
select top 1 @j=f5,@k=id from #t where f5<>0 order by id
if @j>=@l
begin
insert #t1 select @i,f1,f2,f3,f4,@l,f from #t where id=@k
update #t set f5=f5-@l where id=@k
select @i=@i+1,@l=@num
end
else
begin
insert #t1 select @i,f1,f2,f3,f4,f5,f from #t where id=@k
update #t set f5=0 where id=@k
set @l=@l-@j
end
endselect *,type1+type2+type3+type4 as NUM from (
select N,f1,f2,f3,f4,
sum(case when f=1 then f5 else 0 end) as TYPE1,
sum(case when f=2 then f5 else 0 end) as TYPE2,
sum(case when f=3 then f5 else 0 end) as TYPE3,
sum(case when f=4 then f5 else 0 end) as TYPE4
from #t1
group by N,f1,f2,f3,f4
) A
order by Ndrop table #t1
drop table #t
declare @a table(f1 varchar(10),f2 varchar(10),f3 varchar(10),f4 varchar(10),f5 int,f6 int,f7 int,f8 int)
insert @a select '28729', 'H-1892', 'H-16620', 'S',14, 6, 13,25
insert @a select '28729', 'H-1892', 'H-16621', 'N',3,45,25,8
我所给出的货物表只是取了二条记录,且鞋码的字段数相应减少了16个。如果要实现这张货物表的排箱功能的话,teaism() 还有没有更好的方法呢?还望多多指教一下我这个菜鸟哦,菜鸟再次感谢
{
if(包含物品i是可以接受的)
{将物品i计算入内
if (i<n-1) -- n为物品总数
{try(物品i,当前选择已达到的重量和+i的数量)}
else
恢复i不包括在内状态
}
}
货物单号 鞋子款式 存放仓号 鞋子宽度 鞋子码数1 鞋子码数2 鞋子码数3 鞋子码数4
28729 H-1892 H-16620 S 14 6 13 25
28729 H-1892 H-16621 N 3 45 25 8
你的货物表就这样的,
那上面做的就没错。主要是看你实际中货物表的结构如何。如果确定为上面样子。就没错。
declare @a table(货物单号 varchar(10),鞋子款式 varchar(10),存放仓号 varchar(10),鞋子宽度 varchar(10),码数1 int,码数2 int,码数3 int,码数4 int)
insert @a select '28729', 'H-1892', 'H-16620', 'S',14, 6, 13,25
insert @a select '28729', 'H-1892', 'H-16621', 'N',3,45,25,8--处理过程:
select * into #t from (
select 货物单号,鞋子款式,存放仓号,鞋子宽度,码数1 as 数量,1 as 码数标志 from @a
union all
select 货物单号,鞋子款式,存放仓号,鞋子宽度,码数2,2 from @a
union all
select 货物单号,鞋子款式,存放仓号,鞋子宽度,码数3,3 from @a
union all
select 货物单号,鞋子款式,存放仓号,鞋子宽度,码数4,4 from @a
) A order by 货物单号,鞋子款式,存放仓号,鞋子宽度,码数标志exec('alter table #t add id int identity(1,1)')select top 0 0 as N,货物单号,鞋子款式,存放仓号,鞋子宽度,数量,码数标志 into #t1 from #t
declare @装箱数量 int
set @装箱数量=18-----------------------------@装箱数量declare @i int,@j int,@k int,@l int
select @i=1,@l=@装箱数量
while exists(select 1 from #t where 数量<>0)
begin
select top 1 @j=数量,@k=id from #t where 数量<>0 order by id
if @j>=@l
begin
insert #t1 select @i,货物单号,鞋子款式,存放仓号,鞋子宽度,@l,码数标志 from #t where id=@k
update #t set 数量=数量-@l where id=@k
select @i=@i+1,@l=@装箱数量
end
else
begin
insert #t1 select @i,货物单号,鞋子款式,存放仓号,鞋子宽度,数量,码数标志 from #t where id=@k
update #t set 数量=0 where id=@k
set @l=@l-@j
endend
select *,码数1+码数2+码数3+码数4 as 出货数 from (
select N,货物单号,鞋子款式,存放仓号,鞋子宽度,
sum(case when 码数标志=1 then 数量 else 0 end) as 码数1,
sum(case when 码数标志=2 then 数量 else 0 end) as 码数2,
sum(case when 码数标志=3 then 数量 else 0 end) as 码数3,
sum(case when 码数标志=4 then 数量 else 0 end) as 码数4
from #t1 tt where exists(select 1 from #t1 group by N having sum(数量)=18 and N=tt.N)
group by N,货物单号,鞋子款式,存放仓号,鞋子宽度
union all
select N,货物单号,鞋子款式,存放仓号,鞋子宽度,
case when 码数标志=1 then 数量 else 0 end as 码数1,
case when 码数标志=2 then 数量 else 0 end as 码数2,
case when 码数标志=3 then 数量 else 0 end as 码数3,
case when 码数标志=4 then 数量 else 0 end as 码数4
from #t1 tt where exists(select 1 from #t1 group by N having sum(数量)<18 and N=tt.N)
) A
order by 货物单号,鞋子款式,存放仓号,鞋子宽度godrop table #t1
drop table #t
declare @a table(f1 varchar(10),f2 varchar(10),f3 varchar(10),f4 varchar(10),f5 int,f6 int,f7 int,f8 int,f9 int)insert @a select '28729', 'H-1892', 'H-16620', 'S',14, 6, 13,null,25
insert @a select '28729', 'H-1892', 'H-16621', 'N',3,45,25,8,null
--处理过程:select identity(int,1,1) as id,f1,f2,f3,f4,
isnull(f5,0) as f5,
isnull(f6,0) as f6,
isnull(f7,0) as f7,
isnull(f8,0) as f8,
isnull(f9,0) as f9
into #t from @aselect top 0 0 as N,f1,f2,f3,f4,f5,f6,f7,f8,f9 into #t1 from #t declare @num int
set @num=18--装箱数量
declare @i int,@k int,@l int
declare @f5 int,@f6 int,@f7 int,@f8 int,@f9 int
select @i=1,@l=@num
while exists(select 1 from #t where f5<>0 or f6<>0 or f7<>0 or f8<>0 or f9<>0)
begin
select top 1 @f5=f5,@f6=f6,@f7=f7,@f8=f8,@f9=f9,@k=id from #t where f5<>0 or f6<>0 or f7<>0 or f8<>0 or f9<>0 order by id
while @f5>0
begin
if @f5>=@l
begin
insert #t1 select @i,f1,f2,f3,f4,@l,0,0,0,0 from #t where id=5
update #t set f5=f5-@l where id=@k
select @i=@i+1,@f5=@f5-@l
set @l=@num
end
else
begin
insert #t1 select @i,f1,f2,f3,f4,f5,0,0,0,0 from #t where id=@k
update #t set f5=0 where id=@k
select @l=@l-@f5,@f5=0
end
end
while @f6>0
begin
if @f6>=@l
begin
insert #t1 select @i,f1,f2,f3,f4,0,@l,0,0,0 from #t where id=@k
update #t set f6=f6-@l where id=@k
select @i=@i+1,@f6=@f6-@l
set @l=@num
end
else
begin
insert #t1 select @i,f1,f2,f3,f4,0,f6,0,0,0 from #t where id=@k
update #t set f6=0 where id=@k
select @l=@l-@f6,@f6=0
end
end
while @f7>0
begin
if @f7>=@l
begin
insert #t1 select @i,f1,f2,f3,f4,0,0,@l,0,0 from #t where id=@k
update #t set f7=f7-@l where id=@k
select @i=@i+1,@f7=@f7-@l
set @l=@num
end
else
begin
insert #t1 select @i,f1,f2,f3,f4,0,0,f7,0,0 from #t where id=@k
update #t set f7=0 where id=@k
select @l=@l-@f7,@f7=0
end
end
while @f8>0
begin
if @f8>=@l
begin
insert #t1 select @i,f1,f2,f3,f4,0,0,0,@l,0 from #t where id=@k
update #t set f8=f8-@l where id=@k
select @i=@i+1,@f8=@f8-@l
set @l=@num
end
else
begin
insert #t1 select @i,f1,f2,f3,f4,0,0,0,f8,0 from #t where id=@k
update #t set f8=0 where id=@k
select @l=@l-@f8,@f8=0
end
end
while @f9>0
begin
if @f9>=@l
begin
insert #t1 select @i,f1,f2,f3,f4,0,0,0,0,@l from #t where id=@k
update #t set f9=f9-@l where id=@k
select @i=@i+1,@f9=@f9-@l
set @l=@num
end
else
begin
insert #t1 select @i,f1,f2,f3,f4,0,0,0,0,f9 from #t where id=@k
update #t set f9=0 where id=@k
select @l=@l-@f9,@f9=0
end
end
end
select n,f1,f2,f3,f4,sum(f5) as f5
,sum(f6) as f6
,sum(f7) as f7
,sum(f8) as f8
,sum(f9) as f9
,sum(f5+f6+f7+f8+f9) as tt
from #t1
group by n,f1,f2,f3,f4
order by n,f1,f2,f3,f4
drop table #t1
drop table #t