sql - 基于集的方法将付款应用于账单

我试图显示适用于账单的付款方式，我想知道是否有一种无需使用游标和命令式逻辑即可完成此操作的方法。我有一个“账单”表和“付款”表。付款并不总是以账单金额为准，有时多于账单，有时少于账单。我正在尝试创建某种联接，以显示每次付款中有多少已应用于每个账单。

假设:

帐单属于一个帐户

按账单ID

的顺序将付款应用于每个账单

给定一个Bills表:

ID  Amount
1   500
2   500
3   500

方案1:

付款表

ID  Amount
1   750
2   750

使用上面的Bills表和方案1 Payments表，我希望看到以下输出:

Bill ID | Payment ID | Amount Applied
1   1   500
2   2   250
3   2   500
3   2   250

方案2:

Payments table:
1   300
2   300
3   300
4   300
5   300

鉴于上面的“帐单”表和“方案2付款”表，我希望看到以下输出:

Bill ID | Payment ID | Amount Applied
1   1   300
1   2   200
2   2   100
2   3   300
2   4   100
3   4   200
3   5   300

我可以使用游标执行此操作，但是我想了解是否有人知道如何使用基于集合的SQL来执行此操作。

谢谢!

最佳答案

SQL 2005需要更多的步骤，因为它缺少SUM() OVER(ORDER BY...)和LAG()函数，但是基本思想是相同的:计算“账单和付款”的运行总计。

每个帐单可以处于以下三种状态之一:未付，全额付清或部分付清。比较运行总计将确定状态。当前状态和先前状态之间的差异将是当前周期中应用于该账单的付款金额。

SQL 2005的

WITH
  Bills_RunningTotal AS (
    SELECT
       b_ID = b1.ID
      ,b_Amount = b1.Amount
      ,b_RunningTotal = SUM(b2.Amount)
    FROM Bills b1
    INNER JOIN Bills b2
      ON (b2.ID <= b1.ID)
    GROUP BY b1.ID,b1.Amount
  )
 ,Payments_RunningTotal AS (
    SELECT
       p_ID = p1.ID
      ,p_Amount = p1.Amount
      ,p_RunningTotal = SUM(p2.Amount)
    FROM Payments p1
    INNER JOIN Payments p2
      ON (p2.ID <= p1.ID)
    GROUP BY p1.ID,p1.Amount
  )
 ,Bills_Payments_RemainderDue AS (
    SELECT
      b_ID
     ,p_ID
     ,b_Previous_Remainder_Due = CASE
       WHEN b_RunningTotal + p_Amount < p_RunningTotal THEN 0
       WHEN b_RunningTotal + p_Amount > p_RunningTotal + b_Amount THEN b_Amount
       ELSE b_RunningTotal + p_Amount - p_RunningTotal
     END
     ,b_Current_Remainder_Due = CASE
       WHEN b_RunningTotal < p_RunningTotal THEN 0
       WHEN b_RunningTotal > p_RunningTotal + b_Amount THEN b_Amount
       ELSE b_RunningTotal - p_RunningTotal
     END
     FROM Bills_RunningTotal b
     CROSS JOIN Payments_RunningTotal p
  )
SELECT
  [Bill ID] = b_ID
 ,[Payment ID] = p_ID
 ,[Amount Applied] = b_Previous_Remainder_Due - b_Current_Remainder_Due
FROM Bills_Payments_RemainderDue
WHERE b_Previous_Remainder_Due - b_Current_Remainder_Due > 0
ORDER BY [Bill ID],[Payment ID]

SQL 2008+

使用有序窗口函数，可以使查询效率更高。

WITH
  Bills_Payments_RunningTotal AS (
    SELECT
       b_ID = b.ID
      ,p_ID = p.ID
      ,b_Amount = b.Amount
      ,p_Amount = p.Amount
      ,b_RunningTotal = SUM(b.Amount) OVER(PARTITION BY p.ID ORDER BY b.ID)
      ,p_RunningTotal = SUM(p.Amount) OVER(PARTITION BY b.ID ORDER BY p.ID)
    FROM Bills b
    CROSS JOIN Payments p
  )
 ,Bills_Payments_RemainderDue AS (
    SELECT b_ID,p_ID,b_Amount,p_Amount
     ,b_Current_Remainder_Due = CASE
        WHEN b_RunningTotal < p_RunningTotal THEN 0
        WHEN b_RunningTotal > p_RunningTotal + b_Amount THEN b_Amount
        ELSE b_RunningTotal - p_RunningTotal
      END
    FROM Bills_Payments_RunningTotal
  )
 ,Bills_Payments_AmountApplied AS (
    SELECT
      [Bill ID] = b_ID
     ,[Payment ID] = p_ID
     ,[Amount Applied] = ISNULL(LAG(b_Current_Remainder_Due) OVER(PARTITION BY b_ID ORDER BY p_ID),b_Amount) - b_Current_Remainder_Due
    FROM Bills_Payments_RemainderDue
  )
SELECT [Bill ID],[Payment ID],[Amount Applied]
FROM Bills_Payments_AmountApplied
WHERE [Amount Applied] > 0
ORDER BY [Bill ID],[Payment ID]

关于sql - 基于集的方法将付款应用于账单，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/30354743/