最近我会遇到这样一个问题，类似于将发票上的金额分配到单据上。我将解决的核心部分抽取出来，在此做个标记。
注：最后一列是我们想要的值
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
