关于一个商品分配的疑难问题

Question

现在有一应用： 
表A:队列号 订单号 商品号 
    001    A001    aaa       
    002    A001    bbb       
    003    A002    aaa  表B：订单号 商品号 订购数量
     A001    aaa     30
     A002    aaa     80表C:商品号 库房名 库存数量 
     aaa    上海     60
     aaa    北京     40 如何分配库存数量：即SQL语句返回
队列号  库房名  分配数量
001     上海    30
003     上海    30
003     北京    40考虑到性能问题，不想使用游标，不知道大家有什么好方法没有，麻烦大家不吝赐教，谢谢！ 

Answer 1

--游标。。
--> 测试数据：[表A]
if object_id('[表A]') is not null drop table [表A]
create table [表A]([队列号] varchar(3),[订单号] varchar(4),[商品号] varchar(3))
go
insert [表A]
select '001','A001','aaa' union all
select '002','A001','bbb' union all
select '003','A002','aaa'
--> 测试数据：[表B]
if object_id('[表B]') is not null drop table [表B]
create table [表B]([订单号] varchar(4),[商品号] varchar(3),[订购数量] int)
go
insert [表B]
select 'A001','aaa',30 union all
select 'A002','aaa',80
--> 测试数据：[表C]
if object_id('[表C]') is not null drop table [表C]
create table [表C]([商品号] varchar(3),[库房名] varchar(4),[库存数量] int)
go
insert [表C]
select 'aaa','上海',60 union all
select 'aaa','北京',40select 
id=identity(int,1,1),
t.*,r.订购数量
into #temp
from [表A] t join [表B] r
on t.订单号 = r.订单号 and t.商品号 = r.商品号
order by t.队列号declare @city varchar(20),@nums varchar(20),@i int
declare @return table(队列号 varchar(20), 库房名 varchar(20),  分配数量 int)select @i = 1DECLARE Cursor_temp Cursor For 
select [库房名],[库存数量] from [表C]
OPEN Cursor_temp
FETCH  Cursor_temp Into @city,@nums
while @@fetch_status = 0
begin
while @nums > 0 
begin 
insert into @return
select 队列号,@city,case when @nums > 订购数量 then 订购数量 else @nums end
from #temp
where id = @i
select @nums = case when @nums <= 订购数量 then 0 else @nums - 订购数量 end from #temp
where id = @i update #temp set 订购数量 = case when 订购数量 > @nums then 订购数量 - @nums else 0 end
where id = @i if not exists(select 1 from #temp where id=@i and 订购数量 > 0 )
begin
select @i = @i + 1
end
end
fetch next from Cursor_temp into @city,@nums
end
close Cursor_temp
deallocate Cursor_tempselect * from @returndrop table #temp
--------------------------------
001 上海 30
003 上海 30
003 北京 40

Answer 2

--更正，有点小问题
--游标。。
--> 测试数据：[表A]
if object_id('[表A]') is not null drop table [表A]
create table [表A]([队列号] varchar(3),[订单号] varchar(4),[商品号] varchar(3))
go
insert [表A]
select '001','A001','aaa' union all
select '002','A001','bbb' union all
select '003','A002','aaa'
--> 测试数据：[表B]
if object_id('[表B]') is not null drop table [表B]
create table [表B]([订单号] varchar(4),[商品号] varchar(3),[订购数量] int)
go
insert [表B]
select 'A001','aaa',30 union all
select 'A002','aaa',80
--> 测试数据：[表C]
if object_id('[表C]') is not null drop table [表C]
create table [表C]([商品号] varchar(3),[库房名] varchar(4),[库存数量] int)
go
insert [表C]
select 'aaa','上海',60 union all
select 'aaa','北京',40select 
id=identity(int,1,1),
t.*,r.订购数量
into #temp
from [表A] t join [表B] r
on t.订单号 = r.订单号 and t.商品号 = r.商品号
order by t.队列号declare @city varchar(20),@nums int,@i int,@old int
declare @return table(队列号 varchar(20), 库房名 varchar(20),  分配数量 int)select @i = 1DECLARE Cursor_temp Cursor For 
select [库房名],[库存数量] from [表C]
OPEN Cursor_temp
FETCH  Cursor_temp Into @city,@nums
while @@fetch_status = 0
begin
    while @nums > 0 and exists(select 1 from #temp where 订购数量 > 0 )
    begin 
select @old = @nums
        insert into @return
        select 队列号,@city,case when @nums > 订购数量 then 订购数量 else @nums end
        from #temp
        where id = @i        select @nums = case when @nums <= 订购数量 then 0 else @nums - 订购数量 end from #temp
        where id = @i

        update #temp set 订购数量 = case when 订购数量 > @old then 订购数量 - @old else 0 end
        where id = @i        if not exists(select 1 from #temp where id=@i and 订购数量 > 0 )
        begin
            select @i = @i + 1
        end
    end            
    fetch next from Cursor_temp into @city,@nums
end
close Cursor_temp
deallocate Cursor_tempselect * from @returndrop table #temp----------------------------
001 上海 30
003 上海 30
003 北京 40

Answer 3

游标在大数据量时会是灾难
可以用MS SQL指令完成，2000、2005、2008均可，有偿支持