想测试一下你的SQL技术水平吗？相当能考验你能力的面试问题，请有兴趣的朋友进来挑战一下。
数据库是SQLServer，有一个基本表1如下
描述：英文字段名(中文描述)
PNO(生产单号) PQty(待产量) DayQty(机器日产量) 生产次序
001 100 40 1
002 30 20 2
003 20 10 3
想生成如下的推断计划表2：
Day(第几天) PNO(生产单号) AQty(安排产量)
1 001 40
1 002 0
1 003 0
2 001 40
2 002 0
2 003 0
3 001 20
3 002 10
3 003 0
4 002 20
4 003 0
5 003 10
6 003 10
看懂了吗？我解释一下：凡是到某天还没生产完成的单(包括生产了一部分的或者还没开始生产的)，那天的数据都要包括这些单号，生产完成的单则不必显示，如果该单当天按推断不会生产，则AQty(安排产量)为0，请大家看看第3天的数据，该天001单剩下20个没做，机器对001单的日产量是40个，那么机器能够生产完001单之后还剩下半天时间，按照生产次序，001单完成之后是002单，002单要生产30个，机器对002单的日产量是20个，即是说，第3天还有半天时间可以安排生产002单，这半天时间能够生产002单是10个，002单还剩20个，这20个刚好第4天可以生产完，那么第5天就可以开始生产003单
逻辑就是这样，好像并不复杂，但实际操作下来却发现问题远远没有那么简单，有兴趣的朋友，可以自己建立以上的测试表1，如果可以生产计划表2则证明正确，现实正式表的字段和数据远远比测试表大得多，但基本逻辑是一样的，由于海量数据还要考虑效率问题（这个我们可以晚一步去考虑），首要任务是能够生成表2，怎么样？有兴趣挑战一下自己吗？如果能做到的朋友，可以帮忙优化，以尽量少的语句尽量快的实现吗？
[解决办法]
貌似跟这个差不多了..自己参考.問題描述：/*表：@ASN QTY---------- -----------011001 35022002 27*//*需求結果：SN T_NO T_QTY---------- ----------- -----------011001 1 12011001 2 12011001 3 11022002 1 12022002 2 12022002 3 3*/簡單說下規則。某种物品(011001)有35件，12件為一打，每一打出一張條碼，條碼有序號（不同物品從1開始），顯示個數為12。如果最後餘數不夠12件，也要出一張條碼，顯示個數為餘數（例如需求結果T_QTY中的11）。為方便各位測試寫好了測試數據：DECLARE @A TABLE( SN NVARCHAR(10), QTY INT)INSERT INTO @ASELECT '011001',35 UNION ALLSELECT '022002',27SELECT * FROM @A/*SN QTY---------- -----------011001 35022002 27*/DECLARE @A TABLE( SN NVARCHAR(10), QTY INT)INSERT INTO @ASELECT '011001',35 UNION ALLSELECT '022002',27--2005;WITH Liang AS( SELECT SN, QTY, QTY-12 AS A, CASE WHEN QTY>=12 THEN 12 ELSE QTY END AS B, level=1 FROM @A UNION ALL SELECT A.SN, A.QTY, B.A-12, CASE WHEN B.A>=12 THEN 12 ELSE B.A END, level+1 FROM @A AS A JOIN Liang AS B ON A.SN=B.SN AND B.A>0)SELECT SN, QTY, B AS T_QTY, level AS T_NOFROM LiangORDER BY SN,level--2000SELECT TOP 50 ID=IDENTITY(INT,0,1) INTO # FROM sysobjects;SELECT A.SN, A.QTY, B.ID+1 AS T_NO, CASE WHEN A.QTY-12*B.ID>=12 THEN 12 ELSE A.QTY-12*B.ID END AS T_QTYFROM @A AS A JOIN # AS B ON A.QTY-12*B.ID>0 DROP TABLE #/*SN QTY T_QTY T_NO---------- ----------- ----------- -----------011001 35 12 1011001 35 12 2011001 35 11 3022002 27 12 1022002 27 12 2022002 27 3 3(6 行受影响)*/
[解决办法]
--没有测试环境，就只能随手写游标的了。。。。--当天不生产的没排进去，这个好办的，暂时只排能生产的。。。--谁帮忙跑下，看看能不能跑过先~~~，哈哈~~declare @pno varchar(03),@pqty int,@dayqty int,@id intdeclare @i int, @rate numeric(9,2)set @i=1set @rate=1.00declare @t table([day] int, pno varchar(03),aqty int)declare c1 cursor forselect pno, pqty, dayqty, idfrom Topen c1fetch next from c1 into @pno,@pqty,@dayqty,@idwhile @@fetch_status=0begin while @pqty>0 begin if @pqty>=@dayqty*@rate begin insert into @t select @i,@pno,@dayqty*@rate select @i=@i+1, @pqty=@pqty-@dayqty*@rate select @rate=1.00 end else begin insert into @t select @i,@pno,@pqty select @rate=1- (1.00*@pqty)/@dayqty select @pqty=0 end end fetch next from c1 into @pno,@pqty,@dayqty,@idendclose c1deallocate c1
[解决办法]
create table tb(PNO varchar(10),PQty int,DayQty int,OrderQty int)insert into tb select '001',100,40,1 insert into tb select '002',30,20,2insert into tb select '003',20,10,3go;with cte as(select [Day]=1,PNO,AQty=(case when PQty>DayQty then DayQty else PQty end),PQty=PQty-(case when PQty>DayQty then DayQty else PQty end),OrderQty from tbwhere Orderqty=(select min(OrderQty) from tb)union allselect a.[Day],b.PNO,AQty=(case when a.PQty<=0 then (case when b.PQty>b.DayQty then b.DayQty else b.PQty end) else 0 end),PQty=b.PQty-(case when a.PQty<=0 then (case when b.PQty>b.DayQty then b.DayQty else b.PQty end) else 0 end),b.OrderQty from cte a inner join tb b on a.OrderQty=b.OrderQty-1union allselect [Day]=a.[Day]+1,a.PNO,AQty=(case when a.PQty>b.DayQty then b.DayQty else a.PQty end),PQty=a.PQty-(case when a.PQty>b.DayQty then b.DayQty else a.PQty end),a.OrderQty from cte a inner join tb b on a.PNO=b.PNOwhere a.PQty>0 and a.AQty>0)select * from cte order by [Day],pnogodrop table tb/*Day PNO AQty PQty OrderQty----------- ---------- ----------- ----------- -----------1 001 40 60 11 002 0 30 21 003 0 20 32 001 40 20 12 002 0 30 22 003 0 20 33 001 20 0 13 002 20 10 23 003 0 20 34 002 10 0 24 003 10 10 35 003 10 0 3(12 行受影响)*/
[解决办法]
希望楼主结贴的时候捐给我点可用分，求您了，专家分就给别人吧。
SET NOCOUNT ON --建立测试环境CREATE TABLE #T(PNO char(3),PQty int,DayQty int,SequenceNumber int)INSERT #T(PNO,PQty,DayQty,SequenceNumber)SELECT '001','100','40','1' UNION ALLSELECT '002','30','20','2' UNION ALLSELECT '003','20','10','3'GO--执行测试语句DECLARE @Day FLOAT SELECT @Day = SUM(CONVERT(FLOAT,[PQty])/[DayQty]) FROM #T TSELECT x.[Day],t.*,1.0/t.DayQty AS TimeConsume,0 AS AQty,0 AS FlagINTO #T2FROM [#T] tCROSS JOIN (SELECT ROW_NUMBER() OVER(ORDER BY a.[object_id]) AS Day FROM sys.columns a,sys.columns b)xWHERE x.Day <= CEILING(@Day)ORDER BY x.Day,t.SequenceNumberCREATE UNIQUE CLUSTERED INDEX #IX_T2 ON #T2([Day],SequenceNumber)DECLARE @PQty INT ,@PNO CHAR(3),@AQty INT,@LeftTime FLOAT,@TimeConsume FLOATSELECT TOP 1 @PQty = PQty,@PNO = PNO,@Day = [Day],@TimeConsume = TimeConsume,@LeftTime = 1,@AQty= 0 FROM [#T2]UPDATE [#T2]SET @PNO = CASE WHEN @PQty = 0 THEN PNO ELSE @PNO END ,@PQty = CASE WHEN @PQty = 0 THEN PQty ELSE @PQty END ,@LeftTime = CASE WHEN @Day = [Day] THEN @LeftTime - @AQty*@TimeConsume ELSE 1 END ,@AQty = CASE WHEN @PNO = PNO AND @PQty > 0 AND @LeftTime > 0 THEN CASE WHEN @PQty > DayQty THEN @LeftTime/TimeConsume ELSE @PQty END ELSE 0 END ,@PQty = @PQty - @AQty ,@Day = [Day] ,@TimeConsume = TimeConsume ,Flag = CASE WHEN @PQty = 0 THEN 1 ELSE 0 END ,AQty = @AQtySELECT [Day],[PNO],[AQty] FROM [#T2] a WHERE NOT EXISTS( SELECT 1 FROM [#T2] b WHERE a.[Day] > b.[Day] AND a.PNO = b.PNO AND a.Flag = 0 AND b.Flag = 1 )GO--删除测试环境DROP TABLE #T,#T2GO/*--测试结果Day PNO AQty-------------------- ---- -----------1 001 401 002 01 003 02 001 402 002 02 003 03 001 203 002 103 003 04 002 204 003 05 003 106 003 10(13 行受影响)*/
[解决办法]
看看有什么问题
create table test(PNO varchar(5),PQty int,DayQty int, 生产次序 int)insert test select'001', 100, 40 , 1 union all select'002', 30 , 20 , 2 union all select'003', 20 , 10 , 3 union all select'004', 20 , 100 , 4 union all select'005', 20 , 50 , 5 union all select'006', 10 , 100 , 6 union all select'007', 100 , 200 , 7 union all select'008', 20 , 10 , 8 go--开始计算select * into #t from test create table #r( [Day] int,PNO varchar(5) ,AQty int)declare @i intdeclare @Aqty intdeclare @d floatset @i =1while exists (select 1 from #t where PQty>0)begin insert #r select @i,PNO,0 from #t where PQty>0 order by 生产次序 set @d=1 update r set @AQty = case when @d<=0 then 0 when FLOOR(@d*t.DayQty) >=t.PQty then t.PQty else FLOOR(@d*t.DayQty) end ,@d = @d - 1.0*@AQty/t.DayQty ,AQty = @AQty from #r r,#t t where t.PNO=r.PNO and r.[Day] = @i update t set PQty = t.PQty - r.AQty from #t t,#r r where t.PNO=r.PNO and r.[Day] = @i set @i = @i +1end--显示结果select * from #rdrop table #rdrop table #t--结果Day PNO AQty----------- ----- -----------1 001 401 002 01 003 01 004 01 005 01 006 01 007 01 008 02 001 402 002 02 003 02 004 02 005 02 006 02 007 02 008 03 001 203 002 103 003 03 004 03 005 03 006 03 007 03 008 04 002 204 003 04 004 04 005 04 006 04 007 04 008 05 003 105 004 05 005 05 006 05 007 05 008 06 003 106 004 06 005 06 006 06 007 06 008 07 004 207 005 207 006 107 007 607 008 08 007 408 008 89 008 1010 008 2(52 行受影响)
[解决办法]
--sqlserver运行通过
create table #task
(
生产单号 nchar(3),
待产量 int,
机器日产量 int,
生产次序 int
)
create table #plan
(
第几天 int,
生产单号 nchar(3),
安排产量 int
)
--数据，可使用其他数据进行测试
insert into #task values('001','100','40','1')
insert into #task values('002','30','20','2')
insert into #task values('003','20','10','3')
select * from #task
declare @currentNO nchar(3)
declare @total int
declare @capacityOfday int
declare @capacityOfdayActually int
declare @order int
declare @left int
declare @capacityRate float
declare @day int
set @capacityRate=1
set @day=1
set @left=0
declare c_tk cursor for
select 生产单号,
待产量,
机器日产量,
生产次序
from #task
order by 生产次序
open c_tk
fetch next from c_tk into @currentNO, @total, @capacityOfday, @order
while @@fetch_status = 0
begin
set @left = @total - @left
--实际上每天的生产能力
set @capacityOfdayActually=cast(@capacityOfday * @capacityRate as int)
while @left>0
begin
if(@left>=@capacityOfdayActually)
begin
insert into #plan values(@day,@currentNO,@capacityOfdayActually)
insert into #plan
select @day as 第几天, a.生产单号, 0 as 安排产量 from #task a
where 生产次序>@currentNO
order by a.生产单号
set @left = @left-@capacityOfdayActually
set @day=@day+1
set @capacityOfdayActually=@capacityOfday
set @capacityRate=1
end
else
begin
insert into #plan values(@day,@currentNO,@left)
--当天还能剩下的生产能力比率
set @capacityRate=cast(@capacityOfdayActually-@left as float)/cast(@capacityOfdayActually as float)
set @left = 0
end
end
fetch next from c_tk into @currentNO, @total, @capacityOfday, @order
end
close c_tk
deallocate c_tk
select * from #plan
drop table #task
drop table #plan
