有两个表
1. PurchaseOrderMaster表
PurchaseOrderID PackageAmount InvoiceAmount
--------------- --------------------- ---------------------
1 300.00 100.00
2 700.00 400.00
3 500.00 300.00
2. PurchaseOrderItem表
PurchaseOrderItemID PurchaseOrderID PackageSubTotalAmount ProRataPackageSubTotalAmount
------------------- --------------- --------------------- ----------------------------
1 1 100.00 0.00
2 1 100.00 0.00
3 1 100.00 0.00
4 2 500.00 0.00
5 2 100.00 0.00
6 2 100.00 0.00
7 3 100.00 0.00
8 3 200.00 0.00
9 3 200.00 0.00
这两个表通过PurchaseOrderID关联，我现在要更新PurchaseOrderItem表中的ProRataPackageSubTotalAmount字段，
例如PurchaseOrderID = 1的数据有3条，这3条数据的PackageSubTotalAmount和等于PurchaseOrderMaster表中的PackageAmount，及（100+100+100）= 300,ProRataPackageSubTotalAmount这个字段的值是这样算的：
ProRataPackageSubTotalAmount = PackageSubTotalAmount * （InvoiceAmount/PackageAmount）,如第一条记录：
ProRataPackageSubTotalAmount = 100*（100/300）= 33.33(保留两位数)，那么PurchaseOrderID = 1的这3条数据的
ProRataPackageSubTotalAmount都应该是33.33，但是这样就产生四舍五入的误差，即：33.33+33.33+33.33=99.99
我要把最后一条数据用InvoiceAmount减去前两条数据的和，即：100-33.33-33.33=33.34，依次类推，PurchaseOrderID=2、3……的数据也是要这样来计算，求在sql怎样实现，可以用游标，求大侠帮忙啊，这个update语句真是难倒我了……，在线等
------解决方案--------------------
- SQL code
--> 测试数据：#PurchaseOrderMasterif object_id('tempdb.dbo.#PurchaseOrderMaster') is not null drop table #PurchaseOrderMastercreate table #PurchaseOrderMaster(PurchaseOrderID int, PackageAmount numeric(10,2), InvoiceAmount numeric(10,2))insert into #PurchaseOrderMasterselect 1, 300.00, 100.00 union allselect 2, 700.00, 400.00 union allselect 3, 500.00, 300.00--> 测试数据：#PurchaseOrderItemif object_id('tempdb.dbo.#PurchaseOrderItem') is not null drop table #PurchaseOrderItemcreate table #PurchaseOrderItem(PurchaseOrderItemID int, PurchaseOrderID int, PackageSubTotalAmount numeric(10,2), ProRataPackageSubTotalAmount numeric(10,2))insert into #PurchaseOrderItemselect 1, 1, 100.00, 0.00 union allselect 2, 1, 100.00, 0.00 union allselect 3, 1, 100.00, 0.00 union allselect 4, 2, 500.00, 0.00 union allselect 5, 2, 100.00, 0.00 union allselect 6, 2, 100.00, 0.00 union allselect 7, 3, 100.00, 0.00 union allselect 8, 3, 200.00, 0.00 union allselect 9, 3, 200.00, 0.00;with cte(item,po,ia,maxid,v) as( select a.PurchaseOrderItemID, a.PurchaseOrderID, b.InvoiceAmount, max(a.PurchaseOrderItemID)over(partition by a.PurchaseOrderID), convert(dec(10,2), PackageSubTotalAmount*InvoiceAmount/PackageAmount) from #PurchaseOrderItem a join #PurchaseOrderMaster b on a.PurchaseOrderID=b.PurchaseOrderID), v as( select item, v = case maxid when item then ia-(select sum(v) from cte where po=t.po and item<t.item) else v end from cte t)update #PurchaseOrderItem set ProRataPackageSubTotalAmount = v from #PurchaseOrderItem, v where PurchaseOrderItemID=itemselect * from #PurchaseOrderItem/*PurchaseOrderItemID PurchaseOrderID PackageSubTotalAmount ProRataPackageSubTotalAmount------------------- --------------- --------------------------------------- ---------------------------------------1 1 100.00 33.332 1 100.00 33.333 1 100.00 33.344 2 500.00 285.715 2 100.00 57.146 2 100.00 57.157 3 100.00 60.008 3 200.00 120.009 3 200.00 120.00*/