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

Question

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

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

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

分期付款表:

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

Answer 1

虽然我不完全确定您想要什么，但我试了一下。输出是一个表变量，但它可以很容易地更改为更新查询或其他内容。请试一试，看看是否能得到您想要的结果:

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