已解决，用了游标

SQLSERVER 存储过程

CREATE PROCEDURE dbo.AllocatePaymentsToShipments

AS

BEGIN

    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;

END

GO