想测试一下你的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，怎么样？有兴趣挑战一下自己吗？如果能做到的朋友，可以帮忙优化，以尽量少的语句尽量快的实现吗？

[解决办法]

SQL code

貌似跟这个差不多了..自己参考.描述：/*表：@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 行受影响)*/
[解决办法]


SQL code
--没有测试环境，就只能随手写游标的了。。。。--当天不生产的没排进去，这个好办的，暂时只排能生产的。。。--谁帮忙跑下，看看能不能跑过先~~~，哈哈~~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 
 
[解决办法]
SQL code
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 行受影响)*/
[解决办法]
希望楼主结贴的时候捐给我点可用分，求您了，专家分就给别人吧。
SQL code
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 行受影响)*/ 
 
[解决办法]
看看有什么问题

SQL code
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