回答：SQLSERVER 存储过程CREATE PROCEDURE dbo.AllocatePaymentsToShipmentsASBEGIN SET NOCOUNT ON; -- 假设存在两个表：Shipments 和 Payments -- Shipments 表有 ShipmentID, Amount, ShipmentDate -- Payments 表有 PaymentID, Amount, PaymentDate, SalesOrderID -- 临时表存储结果 CREATE TABLE #Result ( ShipmentID INT, AllocatedAmount DECIMAL(18, 2), AllocatedPaymentDate DATE ); -- 变量用于跟踪当前处理的Payment和余额 DECLARE @CurrentPaymentID INT, @CurrentPaymentAmount DECIMAL(18, 2), @RemainingPaymentAmount DECIMAL(18, 2), @CurrentPaymentDate DATE; -- 初始化变量 SET @RemainingPaymentAmount = 0; -- 假设Payments已按PaymentDate排序，Shipments已按ShipmentDate排序 -- 使用游标或循环遍历Payments和Shipments，这里使用CTE和窗口函数模拟 -- 假设Payments已经根据SalesOrderID和PaymentDate排序 WITH SortedPayments AS ( SELECT PaymentID, Amount, PaymentDate, ROW_NUMBER() OVER (PARTITION BY SalesOrderID ORDER BY PaymentDate) AS PaymentOrder FROM Payments ), SortedShipments AS ( SELECT ShipmentID, Amount, ShipmentDate, ROW_NUMBER() OVER (ORDER BY ShipmentDate) AS ShipmentOrder FROM Shipments ), Allocations AS ( -- 这里需要一个复杂的逻辑来逐步分配付款，这里仅展示框架 SELECT sp.ShipmentID, sp.Amount, sp.ShipmentDate, CASE -- 分摊逻辑，这里需要根据实际情况实现 WHEN @RemainingPaymentAmount >= sp.Amount THEN sp.Amount WHEN @RemainingPaymentAmount > 0 THEN @RemainingPaymentAmount ELSE 0 END AS AllocatedAmount, CASE WHEN @RemainingPaymentAmount >= sp.Amount THEN @CurrentPaymentDate WHEN @RemainingPaymentAmount > 0 THEN @CurrentPaymentDate ELSE NULL END AS AllocatedPaymentDate, -- 更新剩余金额的逻辑 @RemainingPaymentAmount = CASE WHEN @RemainingPaymentAmount >= sp.Amount THEN @RemainingPaymentAmount - sp.Amount WHEN @RemainingPaymentAmount > 0 THEN 0 ELSE @RemainingPaymentAmount END FROM SortedShipments sp CROSS APPLY ( SELECT TOP 1 @CurrentPaymentID, @CurrentPaymentAmount, @CurrentPaymentDate FROM SortedPayments WHERE SalesOrderID = -- 假设有方式关联SalesOrderID到Shipment AND PaymentOrder = ( SELECT MAX(PaymentOrder) FROM SortedPayments WHERE SalesOrderID = -- 关联SalesOrderID AND @RemainingPaymentAmount < Amount OR @RemainingPaymentAmount = 0 ) ORDER BY PaymentOrder ) ca -- 注意：这里的CROSS APPLY只是示意，实际实现会更复杂 ) -- 将分配结果插入到临时表 INSERT INTO #Result (ShipmentID, AllocatedAmount, AllocatedPaymentDate) SELECT ShipmentID, AllocatedAmount, AllocatedPaymentDate FROM Allocations WHERE AllocatedAmount > 0; -- 后续处理，比如从临时表返回结果 SELECT * FROM #Result; -- 清理临时表 DROP TABLE #Result;ENDGO
