日期:2014-05-18  浏览次数:20557 次

想测试一下你的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
貌似跟这个差不多了..自己参考.

問題描述:
/*表:@A
SN         QTY
---------- -----------
011001     35
022002     27
*/

/*需求結果:
SN         T_NO        T_QTY
---------- ----------- -----------
011001     1           12
011001     2           12
011001     3           11
022002     1           12
022002     2           12
022002     3           3
*/

簡單說下規則。
某种物品(011001)有35件,12件為一打,每一打出一張條碼,條碼有序號(不同物品從1開始),顯示個數為12。
如果最後餘數不夠12件,也要出一張條碼,顯示個數為餘數(例如需求結果T_QTY中的11)。

為方便各位測試寫好了測試數據:

DECLARE @A TABLE
(
    SN NVARCHAR(10),
    QTY INT
)

INSERT INTO @A
SELECT '011001',35 UNION ALL
SELECT '022002',27

SELECT * FROM @A
/*
SN         QTY
---------- -----------
011001     35
022002     27
*/

DECLARE @A TABLE
(
    SN NVARCHAR(10),
    QTY INT
)

INSERT INTO @A
SELECT '011001',35 UNION ALL
SELECT '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_NO
FROM Liang
ORDER BY SN,level

--2000

SELECT 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_QTY
FROM @A AS A
    JOIN # AS B
        ON A.QTY-12*B.ID>0
        
DROP TABLE #


/*
SN         QTY         T_QTY       T_NO
---------- ----------- ----------- -----------
011001     35          12          1
011001     35          12          2
011001     35          11          3
022002     27          12          1
022002     27          12          2
022002     27          3           3

(6 行受影响)

*/

------解决方案--------------------


SQL code

--没有测试环境,就只能随手写游标的了。。。。
--当天不生产的没排进去,这个好办的,暂时只排能生产的。。。
--谁帮忙跑下,看看能不能跑过先~~~,哈哈~~
declare @pno varchar(03),@pqty int,@dayqty int,@id int
declare @i int, @rate numeric(9,2)
set @i=1
set @rate=1.00
declare @t table([day] int, pno varchar(03),aqty int)

declare c1 cursor for
select pno, pqty, dayqty, id
from T
open c1
fetch next from c1 into @pno,@pqty,@dayqty,@id
while @@fetch_status=0
begin
 while @pqty>0
 begin
     if @pqty>=@dayqty*@rate 
        begin
        insert into @t select @i,@pno,@dayqty*@rat