分配数量/金额

最近我会遇到这样一个问题，类似于将发票上的金额分配到单据上。我将解决的核心部分抽取出来，在此做个标记。

注：最后一列是我们想要的值

aId aCode aAmt bId bCode bAmt bRealAmt

1 xout0001 900.00 1 pin0001 500.00 500.00

1 xout0001 900.00 2 pin0002 200.00 200.00

2 xout0002 500.00 3 pin0003 600.00 500.00

2 xout0002 500.00 4 pin0004 100.00 0.00

3 xout0003 600.00 5 pin0005 600.00 600.00

 

CREATE TABLE tInvoice
    (
      OID BIGINT IDENTITY,
      vCode NVARCHAR(50),
      vName NVARCHAR(50),
      dcAmt DECIMAL(38, 10)
    )
GO

CREATE TABLE tBill
    (
      OID BIGINT IDENTITY,
      vCode NVARCHAR(50),
      lInvoiceId BIGINT,
      dcAmt DECIMAL(38, 10),
      dcRealAmt DECIMAL(38, 10)
    )
GO

INSERT  INTO tInvoice ( vCode, vName, dcAmt )
SELECT  'xout0001', 'xout0001', 900
UNION
SELECT  'xout0002', 'xout0002', 500
UNION
SELECT  'xout0003', 'xout0003', 600

INSERT  INTO tBill (vCode, lInvoiceId, dcAmt)
        SELECT  'pin0001', 1, 500
        UNION
        SELECT  'pin0002', 1, 200
        UNION
        SELECT  'pin0003', 2, 600
        UNION
        SELECT  'pin0004', 2, 100
        UNION
        SELECT  'pin0005', 3, 600

UPDATE  T1
SET     T1.dcRealAmt = 
        CASE WHEN T2.dcAmt >= ISNULL(( SELECT    SUM(t.dcAmt)
                                      FROM      tBill AS t
                                      WHERE     t.lInvoiceId = T1.lInvoiceId
                                                AND t.OID <= T1.OID
                                    ), 0) THEN T1.dcAmt
            WHEN T2.dcAmt <= ISNULL(( SELECT    SUM(t.dcAmt)
                                      FROM      tBill AS t
                                      WHERE     t.lInvoiceId = T1.lInvoiceId
                                                AND t.OID < T1.OID
                                    ), 0) THEN 0
            ELSE T2.dcAmt
                 - ISNULL(( SELECT  SUM(t.dcAmt)
                            FROM    tBill AS t
                            WHERE   t.lInvoiceId = T1.lInvoiceId
                                    AND t.OID < T1.OID
                          ), 0)
       END
FROM    tBill AS T1
        JOIN tInvoice AS T2 ON T1.lInvoiceId = T2.OID

SELECT  a.OID, a.vCode, a.vName, a.dcAmt, b.OID, b.vCode, b.dcAmt, b.dcRealAmt
FROM    tInvoice AS a
    JOIN tBill AS b ON a.OID = b.lInvoiceId
    

--DROP TABLE tBill
--DROP TABLE tInvoice