根據付款次數生成帳單余額

下面是我創建并在其中插入值的表：

CREATE TABLE Invoices     
(
    InvID int,     
    InvAmount int
)
GO    
    
INSERT INTO Invoices 
VALUES (1, 543), (2, 749)

CREATE TABLE payments    
(
    PayID int IDENTITY (1, 1),     
    InvID int,     
    PayAmount int,    
    PayDate date
)

INSERT INTO payments
VALUES (1, 20, '2016-01-01'),
       (1, 35, '2016-01-07'),
       (1, 78, '2016-01-13'),
       (1, 52, '2016-01-25'),    
       (2, 40, '2016-01-03'),
       (2, 54, '2016-01-15'),  
       (2, 63, '2016-01-17'),
       (2, 59, '2016-01-28') 

SELECT * FROM Invoices 
SELECT * FROM payments

As shown in the screenshot above, the Invoice table specifies various customer billings (the first billing totals 543, the second billing totals 749).

As shown in the screenshot above, the payments table specifies the various payments the customer made for each of the billings. For example, one can see that on January 1st the customer paid 20 USD out of billing no. 1 (which totals 543 USD), and on January 3rd the customer paid 40 USD out of billing no. 2, (which totals 749 USD).

Now the question is:

Write a query that displays the billing balance, based on the number of payments made so far.

The query result should exactly look like the screenshot below:

This is what I have tried:

SELECT 
    payments.InvID,
    InvAmount - SUM(PayAmount) OVER (PARTITION BY payments.InvID ORDER BY PayID
                                     ROWS BETWEEN UNBOUNDED PRECEDING AND 0 FOLLOWING) AS 'InvAmount',
    PayDate, PayAmount,
    InvAmount - SUM(PayAmount) OVER (PARTITION BY payments.InvID ORDER BY PayID) AS 'Balance'
FROM 
    Invoices
JOIN 
    payments ON payments.InvID = Invoices.InvID

After running the query, I got the following result which is shown below:

As you can see from the screenshot above, I nearly got the result I wanted.

The only problem is that InvAmount is exactly returning the same row values as Balance. I am not able to retain the starting row values of InvAmount which are 543 (InvID = 1) and 749 (InvID = 2) respectively.

How can this issue be solved?

uj5u.com熱心網友回復：

PayAmount您可以在計算中添加回

InvAmount 
  PayAmount
- SUM(PayAmount) OVER (PARTITION BY payments.InvID 
                           ORDER BY PayID
                                 ROWS BETWEEN UNBOUNDED PRECEDING 
                                          AND 0 FOLLOWING) AS InvAmount

或使用BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING. 但是您需要處理第一行的 NULL 值

InvAmount 
- ISNULL(SUM(PayAmount) OVER (PARTITION BY payments.InvID 
                           ORDER BY PayID
                                 ROWS BETWEEN UNBOUNDED PRECEDING 
                                          AND 1 PRECEDING), 0) AS InvAmount

db>小提琴演示

uj5u.com熱心網友回復：

您可以使用 PARTITION BY 子句作為發票識別符號，然后從發票金額中扣除付款金額，如下所示

;WITH CTE_Balance as
    (
    SELECT i.InvID, i.InvAmount, p.PayAmount, p.PayDate
         , InvAmount - sum(payamount) over (partition by p.invid order by paydate rows between unbounded preceding and current row) as balance 
         , ROW_NUMBER() over(partition by p.invid order by p.paydate) as rnk
    FROM payments as p
    inner join Invoices as i
    on i.InvID = p.InvID
    )
    SELECT invid, case when rnk =1 then invamount else lag(balance) over(partition by invid order by paydate) end as invamount 
           ,payAmount, paydate, balance
    FROM CTE_Balance

標籤：sql sql-server

上一篇：使用SQLServer中的可為空引數執行存盤程序

下一篇：SQLServer中出現以下錯誤資訊“字串后的未閉合引號”如何解決？