T1 ------------------------------ Pno item subQty Qty A01 1 20 20 (subQty<=Qty) A01 2 25 30 A01 3 40 T2 ---------------------------- Pno sitem canQty A01 1 35 A01 2 10 =================================== T3 Pno item sitem Qtyed A01 1 1 20 A01 2 1 15 A01 2 2 10 由T1 T2 得T3並將結果寫回T1(subQty) T1與T2均已在query中(未存在資料庫) 請問各位高手，如何可得到t3的值並寫回t1 THX..^^

若是 t1, t2 不是 table 的話, 則麻煩你先將其轉為 temp table, 由於需求相當複雜, 所以起了一個 cursor 來進行, 配合 t1 的 subQty 欄位來判定已分配的狀況, 這樣比較容易實現. 請先將 t1 的 subQty 都先 update 為 null 後, 再使用後面的 sql command 即可. 若只是要得到 t1 的 subQty 結果的話, 該 command 中有關 t3 的部分可以都移除掉沒有影響, 本次的 sql command 是以 subQty 來當作 meta 欄位進行的, 你先試試看!! 看不懂再問!    

T1
------------------------------                        
Pno        item        subQty        Qty
A01        1          20        20      (subQty<=Qty)
A01        2          25        30
A01        3                40    T2
----------------------------                
Pno        sitem        canQty
A01        1        35
A01        2        10
===================================    T3                        
Pno        item        sitem        Qtyed
A01        1        1        20
A01        2        1        15
A01        2        2        10

    create table #t3
(
  Pno varchar(10),
  item int,
  sitem int, 
  Qtyed int
)    declare @c1Pno varchar(10), @c1sitem int, @c1canQty int, @c2Pno varchar(10), @c2item int, @c2subQty int, @c2Qty int
declare  c1 cursor for select Pno, sitem, canQty from t2 order by Pno, sitem
    open c1
    FETCH NEXT FROM c1 INTO @c1Pno, @c1sitem, @c1canQty
    WHILE @@FETCH_STATUS = 0
    BEGIN
      WHILE @c1canQty > 0 
      BEGIN
        declare c2 cursor for  
        select top 1 Pno, item, isnull(subQty,0), Qty from t1 where Qty - isnull(subQty,0) > 0 and Pno = @c1Pno order by item
        open c2
        FETCH NEXT FROM c2 INTO @c2Pno, @c2item, @c2subQty, @c2Qty
        IF @@FETCH_STATUS=0
        BEGIN
          IF @c1canQty >= @c2Qty - @c2subQty
          BEGIN
            update t1 set subQty = Qty where Pno=@c2Pno and item=@c2item
            insert into #t3 values (@c2Pno, @c2item, @c1sitem, @c2Qty - @c2subQty)
            select @c1canQty = @c1canQty - (@c2Qty - @c2subQty)
          END
          ELSE
          BEGIN
            update t1 set subQty = isnull(subQty,0) @c1canQty where Pno=@c2Pno and item=@c2item
            insert into #t3 values (@c2Pno, @c2item, @c1sitem, @c1canQty)  
            select @c1canQty = 0
          END
        END
        close c2
        deallocate c2
      END
      FETCH NEXT FROM c1 INTO @c1Pno, @c1sitem, @c1canQty
    END
    close c1
    deallocate c1    select * from #t3
drop table #t3    

dear timhuang t1,t2,t3均為資料庫的table 但在分配值動作時，t1,t2的值是暫存在query THX.. 我先試看看..不懂再請教....^^