(续forgot2000帖子)想测试一下你的SQL技术水平吗？相当能考验你能力的面试问题，请有兴趣的朋友进来挑战一下。

Question

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,10
insert into tb select 'A','002',30,20,25
insert into tb select 'A','003',20,10,36
insert into tb select 'B','001',100,30,1
insert 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 a
where 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>0
union 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.Machine
where 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.Machine
where 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.00
from cte a inner join tb b on a.OrderQty<b.OrderQty and a.Machine=b.Machine
where a.PQty>0 
)select Machine,[Day],PNO,AQty,PQty from cte order by Machine,[Day],PNO
go
drop table tb
/*
Machine Day         PNO        AQty                                    PQty
------- ----------- ---------- --------------------------------------- ---------------------------------------
A       1           001        40.00                                   60.00
A       1           002        0.00                                    0.00
A       1           003        0.00                                    0.00
A       2           001        40.00                                   20.00
A       2           002        0.00                                    0.00
A       2           003        0.00                                    0.00
A       3           001        20.00                                   0.00
A       3           002        10.00                                   20.00
A       3           003        0.00                                    0.00
A       4           002        20.00                                   0.00
A       5           003        10.00                                   10.00
A       6           003        10.00                                   0.00
B       1           001        30.00                                   70.00
B       1           002        0.00                                    0.00
B       2           001        30.00                                   40.00
B       2           002        0.00                                    0.00
B       3           001        30.00                                   10.00
B       3           002        0.00                                    0.00
B       4           001        10.00                                   0.00
B       4           002        6.67                                    33.33
B       5           002        10.00                                   23.33
B       6           002        10.00                                   13.33
B       7           002        10.00                                   3.33
B       8           002        3.33                                    0.00
*/

Answer 1

这个程序看上去麻烦,但是可以和任一个用游标的/用临时表的/循环的程序比运行速度.
另,本程序在MSSQL2005及以上版本运行.

Answer 2

请问如何在SQLServer2000上实现，手头上并没有SQLServer2005的测试环境。

Answer 3

与其说SQL技术水平，不如说逻辑能力. 

Answer 4

按照扬帆的方法，改写如下，结果跟qianjin036a稍有不同，性能上区别明显吗（在海量数据下）create table test(
machine varchar(10),
PNO varchar(5),
PQty  decimal(18,2),
DayQty decimal(18,2),  
CXNo int
)insert test select
'A' ,'001',              100,            40 ,                  10 
union all select
'A','002',              30 ,             20 ,                   25 
union all select
'A','003',              20 ,             10 ,                   36 
Union all select
'B' ,'001',              100,            30 ,                  1 
union all select
'B','102',              40 ,             10 ,                   4 go--开始计算select * into #t from test create table #r( 
[Day] int,
machine varchar(10),
PNO varchar(5)  ,
AQty decimal(18,2)
)declare @i int
declare @Aqty decimal(18,2)
declare @d decimal(18,2)
declare @m decimal(18,2)
declare @machine varchar(10)
set @i =1
while exists (select 1 from #t where PQty>0)
begin
    insert #r
    select @i,machine,PNO,0
    from #t
    where PQty>0
    order by machine,CXNo
    
    set @d=1
    set @m=1
    set @machine=''
    update r set
        @m=case when @machine=t.machine then @d else 1 end
       ,@AQty = case when (@m<=0)  then 0 when (@m*t.DayQty) >=t.PQty then t.PQty else (@m*t.DayQty) end
       ,@d = @m - 1.0*@AQty/t.DayQty
       ,AQty = @AQty
       ,@machine=t.machine
    from #r r,#t t
    where t.PNO=r.PNO and t.machine=r.machine
    and r.[Day] = @i
    
    update t set
        PQty = t.PQty - r.AQty
    from #t t,#r r
    where t.PNO=r.PNO and t.machine=r.machine
    and r.[Day] = @i
    set @i = @i +1
    
end--显示结果
select * from #r order by machine,[Day],PNO
drop table  #rdrop table #tdrop table test

Answer 5

格式化一下可能比较方便：
create table test(
machine varchar(10),
PNO varchar(5),
PQty  decimal(18,2),
DayQty decimal(18,2),  
CXNo int
)insert test select
'A' ,'001',              100,            40 ,                  10 
union all select
'A','002',              30 ,             20 ,                   25 
union all select
'A','003',              20 ,             10 ,                   36 
Union all select
'B' ,'001',              100,            30 ,                  1 
union all select
'B','102',              40 ,             10 ,                   4 go--开始计算select * into #t from test create table #r( 
[Day] int,
machine varchar(10),
PNO varchar(5)  ,
AQty decimal(18,2)
)declare @i int
declare @Aqty decimal(18,2)
declare @d decimal(18,2)
declare @m decimal(18,2)
declare @machine varchar(10)
set @i =1
while exists (select 1 from #t where PQty>0)
begin
    insert #r
    select @i,machine,PNO,0
    from #t
    where PQty>0
    order by machine,CXNo
    
    set @d=1
    set @m=1
    set @machine=''
    update r set
        @m=case when @machine=t.machine then @d else 1 end
       ,@AQty = case when (@m<=0)  then 0 when (@m*t.DayQty) >=t.PQty then t.PQty else (@m*t.DayQty) end
       ,@d = @m - 1.0*@AQty/t.DayQty
       ,AQty = @AQty
       ,@machine=t.machine
    from #r r,#t t
    where t.PNO=r.PNO and t.machine=r.machine
    and r.[Day] = @i
    
    update t set
        PQty = t.PQty - r.AQty
    from #t t,#r r
    where t.PNO=r.PNO and t.machine=r.machine
    and r.[Day] = @i
    set @i = @i +1
    
end--显示结果
select * from #r order by machine,[Day],PNO
drop table  #rdrop table #tdrop table test

Answer 6

执行计划不能说明全部问题。处理海量数据应避免书写过于冗长的语句，很容易造成死锁。
5楼那个超长的CTE应用到实际的海量处理时，恐怕吃不消。

Answer 7

可为冗长?
递归,也是循环,不过在不同的条件下进行不同的操作而已,你能觉得17楼的一次
    insert #r 
    select @i,machine,PNO,0 
    from #t 
    where PQty>0 
    order by machine,CXNo 
    
    set @d=1 
    set @m=1 
    set @machine='' 
    update r set 
        @m=case when @machine=t.machine then @d else 1 end 
      ,@AQty = case when (@m <=0)  then 0 when (@m*t.DayQty) >=t.PQty then t.PQty else (@m*t.DayQty) end 
      ,@d = @m - 1.0*@AQty/t.DayQty 
      ,AQty = @AQty 
      ,@machine=t.machine 
    from #r r,#t t 
    where t.PNO=r.PNO and t.machine=r.machine 
    and r.[Day] = @i 
    
    update t set 
        PQty = t.PQty - r.AQty 
    from #t t,#r r 
    where t.PNO=r.PNO and t.machine=r.machine 
    and r.[Day] = @i 
    set @i = @i +1 
比5楼的一次
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.Machine
where 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)执行效率更高吗?

Answer 8

不用执行计划也行,用跟踪来比较:
17楼的结果是:
cpu 63 Reads 1144 Write 0 Duration 71
5楼的是:
cpu 47 Reads 341  Write 0 Duration 47另外,真诚的请教一句(决非恶意!),在逻辑正确的情况下,语句写得长会造成死锁吗?

Answer 9

昨天是单机调度，今天是双机调度，明天再来双机多工序，后天就是n机n工序。
能够搞定这个问题，起码几十万了，还上这儿来面试？
上面的结果，A机器做6天，B机器做8天，从调度的角度来讲，这样的结果肯定是不行的。

Answer 10

递归和循环是有明显区别的，它俩的优劣比较在学校里上数据结构时应该学过。大致来说，递归是书写简便，但效率低于循环。
递归一般利用栈机制，碰到深层嵌套或海量数据时，常常会空间不足而导致崩溃。不过sql2005对CTE做了很多优化，具体情况具体分析吧。