SQL05中如何实现这样的查询
这是一个货物装箱的问题，表2是需要装箱的数据，表1是装箱规则。
根据表1的实际包装件数，生成结果表，最重要的是处理结果表的箱号。
表数据如下，求助。
表1
包装件数箱类型最大件数实际包装件数
41 A1010
41 B2019
41 C1312
表2
DCStroePOSKUQty
shs1po1SKU120
shs1po1SKU221
结果
箱号DCstroePOSKUQty箱类型
1shs1po1SKU110A
2shs1po1SKU110B
2shs1po1SKU29B
3shs1po1SKU212C
[解决办法]
2 sh s1 po1 SKU2 9 B -->A
3 sh s1 po1 SKU2 12 C
--是否正，是A吧
[解决办法]
貌似表1 的第二条数据有误,B的最大件数应该是10,表2中并没有一箱中装多少的设定,所以表1的中间一列还是有意义的.
[解决办法]
给个递归进行工作量分配的程序你参考:
- SQL code
create table tb(Machine varchar(5),PNO varchar(10),PQty decimal(8,2),DayQty decimal(8,2),OrderQty int)insert into tb select 'A','001',100,40,10insert into tb select 'A','002',30,20,25insert into tb select 'A','003',20,10,36insert into tb select 'B','001',100,30,1insert into tb select 'B','002',40,10,4 go;with cte as(--不同机器的第一行select Machine,[Day]=1,PNO,AQty=convert(decimal(8,2),(case when PQty>dayQty then DayQty else PQty end)), PQty=convert(decimal(8,2),PQty-(case when PQty>DayQty then DayQty else PQty end)),OrderQty, Rate=convert(decimal(8,6),(case when PQty<DayQty then 1.0-PQty/DayQty else 0.0 end))from tb awhere OrderQty=(select top 1 OrderQty from tb where machine=a.machine order by OrderQty)union all--当某机器某OrderQty上一次序未生产完时select a.Machine,[Day]=a.[Day]+1,a.PNO,AQty=convert(decimal(8,2),(case when a.PQty>b.DayQty then b.DayQty else a.PQty end)), PQty=convert(decimal(8,2),(case when a.PQty>b.DayQty then a.PQty-b.DayQty else 0.0 end)),a.OrderQty, Rate=convert(decimal(8,6),(case when a.PQty<b.DayQty then 1.0-a.PQty/b.DayQty else 0.0 end))from cte a inner join tb b on a.Machine=b.Machine and a.PNO=b.PNO where a.PQty>0union all--当某机器某QrderQty在某天生产完但剩余生产力时select a.Machine,a.[Day],b.PNO,AQty=convert(decimal(8,2),(case when b.PQty>b.DayQty*a.rate then b.DayQty*a.Rate else b.PQty end)), PQty=convert(decimal(8,2),b.PQty-(case when b.PQty>b.DayQty*a.Rate then b.DayQty*a.Rate else b.PQty end)),b.OrderQty, Rate=convert(decimal(8,6),(case when b.PQty<b.DayQty*a.rate then (b.DayQty*a.Rate-b.PQty)/b.DayQty else 0 end))from cte a inner join tb b on a.Machine=b.Machinewhere a.Rate>0 and a.PQty=0 and a.AQty>0 and a.OrderQty<b.OrderQty and not exists(select 1 from tb where Machine=a.Machine and OrderQty>a.OrderQty and OrderQty<b.OrderQty)union all--当某天生产完某项OrderQty且生产力刚好用完时select a.Machine,[Day]=a.[Day]+1,b.PNO,AQty=convert(decimal(8,2),(case when b.PQty>b.DayQty then b.DayQty else b.PQty end)), PQty=convert(decimal(8,2),b.PQty-(case when b.PQty>b.DayQty then b.DayQty else b.PQty end)),b.OrderQty, Rate=convert(decimal(8,6),(case when b.PQty<b.DayQty then 1.0-b.PQty*1.0/b.DayQty else 0 end))from cte a inner join tb b on a.Machine=b.Machinewhere a.Rate=0 and a.PQty=0 and a.AQty>0 and a.OrderQty<b.OrderQty and not exists(select 1 from tb where Machine=a.Machine and OrderQty>a.OrderQty and OrderQty<b.OrderQty)union all--添加各天不生产的部分select a.Machine,[Day]=a.[day],b.PNO,AQty=0,PQty=0,OrderQty=b.OrderQty,Raty=0.00from cte a inner join tb b on a.OrderQty<b.OrderQty and a.Machine=b.Machinewhere a.PQty>0 )select Machine,[Day],PNO,AQty,PQty from cte order by Machine,[Day],PNOgodrop table tb/*Machine Day PNO AQty PQty------- ----------- ---------- --------------------------------------- ---------------------------------------A 1 001 40.00 60.00A 1 002 0.00 0.00A 1 003 0.00 0.00A 2 001 40.00 20.00A 2 002 0.00 0.00A 2 003 0.00 0.00A 3 001 20.00 0.00A 3 002 10.00 20.00A 3 003 0.00 0.00A 4 002 20.00 0.00A 5 003 10.00 10.00A 6 003 10.00 0.00B 1 001 30.00 70.00B 1 002 0.00 0.00B 2 001 30.00 40.00B 2 002 0.00 0.00B 3 001 30.00 10.00B 3 002 0.00 0.00B 4 001 10.00 0.00B 4 002 6.67 33.33B 5 002 10.00 23.33B 6 002 10.00 13.33B 7 002 10.00 3.33B 8 002 3.33 0.00*/
[解决办法]
- SQL code
create table tb1(id int,flag int,code varchar(10),maxnum int,fnum int)insert into tb1select 1 ,41 ,'A' ,10 ,10 union allselect 2 ,41 ,'B' ,20 ,19 union all select 3 ,41 ,'C' ,13 ,12gocreate table tb2(dc varchar(10),store varchar(10),po varchar(10),sku varchar(10),qty int) insert into tb2select 'sh' ,'s1' ,'po1' ,'SKU1' ,20 union all select 'sh' ,'s1' ,'po1' ,'SKU2' ,21gocreate table cun(id int,dc varchar(10),store varchar(10),po varchar(10),sku varchar(10),qty int,code varchar(10))godeclare my_cursor cursor scrollforselect id,code,fnum from tb1open my_cursordeclare @id intdeclare @code varchar(10)declare @fnum intfetch next from my_cursor into @id,@code,@fnumwhile (@@fetch_status = 0) begin declare next_cursor cursor scroll for select dc,store,po,sku,qty from tb2 open next_cursor declare @dc varchar(10) declare @store varchar(10) declare @po varchar(10) declare @sku varchar(10) declare @qty int declare @gocun int declare @details int declare @decun int set @decun = 0 set @gocun = @fnum fetch next from next_cursor into @dc,@store,@po,@sku,@qty set @gocun = @gocun - @qty while(@@fetch_status = 0) begin if(@gocun = 0) begin set @details = @fnum insert into cun select @id,@dc,@store,@po,@sku,@details,@code fetch next from next_cursor into @dc,@store,@po,@sku,@qty fetch next from my_cursor into @id,@code,@fnum set @gocun = @fnum - @qty set @decun = 0 end if(@gocun > 0) begin set @details = @fnum - @gocun insert into cun select @id,@dc,@store,@po,@sku,@details,@code fetch next from next_cursor into @dc,@store,@po,@sku,@qty set @gocun = @gocun - @qty set @decun = 0 end if(@gocun < 0) begin set @details = @qty + @gocun - @decun insert into cun select @id,@dc,@store,@po,@sku,@details,@code fetch next from my_cursor into @id,@code,@fnum set @gocun = @fnum + @gocun set @decun = @details end end close next_cursor deallocate next_cursor endclose my_cursordeallocate my_cursorselect * from cundrop table tb1,tb2,cun/*****************************id dc store po sku qty code----------- ---------- ---------- ---------- ---------- ----------- ----------1 sh s1 po1 SKU1 10 A2 sh s1 po1 SKU1 10 B2 sh s1 po1 SKU2 9 B3 sh s1 po1 SKU2 12 C3 sh s1 po1 SKU2 12 C(5 行受影响)
[解决办法]
多出来一行？？？