sql-server - sql-显示我可以支付的所有分期付款

我有一张桌子，上面有很多来自不同客户的分期付款。每个客户都有可用余额。

我想显示客户可以支付的所有分期付款。

客户1的可用余额为400
客户2的可用余额为500

分期付款表：

Customer ID       Instalment amount   Available
1                 150                   400
1                 150                   400
1                 150                   400
1                 150                   400
1                 150                   400
1                 150                   400
1                 150                   400
2                 200                   500
2                 200                   500
2                 200                   500
2                 200                   500
2                 200                   500
2                 200                   500

以下是我想要的结果

Customer ID       Instalment amount   Available
1                 150                 400
1                 150                 400
2                 200                 500
2                 200                 500

下面的代码不起作用

DECLARE @DrACAvailable decimal,
        @PayAmount decimal,
        @RunningTotal decimal

SET @RunningTotal = 0

DECLARE rt_cursor CURSOR
FOR
SELECT T2.PayAmount, T2.DrACAvailable
FROM LoanAutoPayTransactions T2
INNER JOIN LoanAutoPayTransactions T1 on T2.LRAC=T1.LRAC


OPEN rt_cursor

FETCH NEXT FROM rt_cursor INTO @PayAmount, @DrACAvailable

WHILE @@FETCH_STATUS = 0
 BEGIN
  SET @RunningTotal = @RunningTotal + @PayAmount
  IF @RunningTotal >= @DrACAvailable BREAK
  UPDATE LoanAutoPayTransactions SET  PayAmount=@RunningTotal WHERE StartDate=(SELECT MIN(T2.StartDate) FROM LoanAutoPayTransactions T2 WHERE T2.LRAC=LRAC)


  FETCH NEXT FROM rt_cursor INTO @PayAmount, @DrACAvailable

 END

CLOSE rt_cursor
DEALLOCATE rt_cursor

最佳答案

虽然我不确定您要什么，但还是尝试了一下。输出是一个表变量，但是可以很容易地将其更改为更新查询或其他内容。请尝试一下，看看是否能提供所需的结果：

DECLARE @DrACAvailable decimal,
        @PayAmount decimal,
        @RunningTotal decimal,
        @CustomerID int,
        @CurrentCustomerID int

SET @RunningTotal = 0

DECLARE @OutputTable table(
    LRAC int,
    PayAmount decimal,
    DrACAvailable decimal);

DECLARE PaymentCursor CURSOR
FOR
SELECT LRAC, PayAmount, DrACAvailable
FROM LoanAutoPayTransactions ORDER BY LRAC, StartDate

OPEN PaymentCursor

FETCH NEXT FROM PaymentCursor INTO @CustomerID, @PayAmount, @DrACAvailable

WHILE @@FETCH_STATUS = 0
 BEGIN
  IF @CurrentCustomerID != @CustomerID SET @RunningTotal = 0
  SET @CurrentCustomerID = @CustomerID
  PRINT 'Processing customer id: ' + CAST(@customerid AS VARCHAR)
  SET @RunningTotal = @RunningTotal + @PayAmount

  IF @RunningTotal <= @DrACAvailable INSERT @OutputTable (LRAC, PayAmount, DrACAvailable) VALUES (@CustomerID, @PayAmount, @DrACAvailable)

  FETCH NEXT FROM PaymentCursor INTO @CustomerID , @PayAmount, @DrACAvailable

 END

CLOSE PaymentCursor
DEALLOCATE PaymentCursor

-- Print the output table
SELECT LRAC AS 'Customer ID', PayAmount AS 'Instalment amount', DrACAvailable AS 'Available' FROM @OutputTable

关于sql-server - sql-显示我可以支付的所有分期付款，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/17650039/