在 T-SQL 中执行运行减法 [英] Performing a running subtraction in T-SQL
问题描述
我坐在两张桌子旁(虽然它们是临时桌子)，看起来像这样:
I'm sitting with two tables (although they're temp-tables) looking like this:
CREATE TABLE [dbo].[Invoice]
(
[InvoiceId] [int] NOT NULL,
[ReceiverId] [int] NOT NULL,
[Amount] [numeric](19, 2) NOT NULL,
[Priority] [int] NOT NULL
);
GO
CREATE TABLE [dbo].[Payment]
(
[PaymentId] [int] NOT NULL,
[SenderId] [int] NOT NULL,
[Amount] [numeric](19, 2) NOT NULL
);
GO
数据可能如下所示:
发票
InvoiceId ReceiverId Amount Priority
1 1 100.00 1
2 1 100.00 2
3 2 100.00 1
4 2 100.00 2
5 1 200.00 3
付款
PaymentId SenderId Amount
1 1 50.00
2 1 45.00
3 2 95.00
4 2 105.00
收到的付款存储在 Payment 中.我的代码的任务是在发件人的发票之间分配 Payment.Amount.
Incoming payments are stored in Payment. My code's task is distributing the Payment.Amount between the sender's invoices.
两者之间的关系键是ReceiverId和SenderId.
The relationship-key between the two is ReceiverId and SenderId.
Priority 列对于每个 ReceiverId 都是唯一的，值1"的优先级高于2".
The Priority column is unique per ReceiverId and the value "1" is of higher priority than "2".
SenderId 为1"的 Payment 行可用于无限数量的 ReceiverId 为1"的发票 - 如果有Payment.Amount 列中的金额不够，他们将根据他们的 Priority 支付.
A Payment row with SenderId "1" could be used on an infinite number of invoices with the ReceiverId "1" - if there's not enough in the Payment.Amount column for all of them they'll be paid in accordance with their Priority.
我正在想办法在不使用循环或游标的情况下对此进行编程.有什么建议?(我正在使用 SQL Server 2014).
I'm trying to think of a way to program this without using a loop or cursor. Any suggestions? (I'm sitting on SQL Server 2014).
我的预期输出是:
1) Payment 1 and 2 would be used to partially pay Invoice 1.
2) Payment 3 would be used to partially pay Invoice 3.
3) Payment 4 would then complete invoice 3.
4) Payment 4 would then completely pay invoice 4.
5) Invoice 2 and 5 would be left completely unpaid.
推荐答案
主要思想
将您的美元金额视为数轴上的间隔.将您的发票和付款以正确的顺序放在彼此相邻的行上.
Think of your dollar amounts as intervals on the number line. Place your Invoices and Payments in correct order on the line adjacent to each other.
发票，收件人/发件人 ID=1
|----100---|----100---|--------200--------|----------->
0 100 200 400
ID 1 2 5
付款，接收方/发送方 ID=1
|-50-|-45|-------------------------------------------->
0 50 95
ID 1 2
将两组间隔放在一起(将它们相交):
Put both sets of intervals together (intersect them):
|----|---|-|----------|-------------------|----------->
0 50 95 100 200 400
现在你有了间隔:
From To InvoiceID PaymentID
------------------------------------
0 50 1 1
50 95 1 2
95 100 1
100 200 2
200 400 5
<小时>
发票，收件人/发件人 ID=2
|----100---|----100---|------------------------------->
0 100 200
ID 3 4
付款，接收方/发送方 ID=2
|--95----|-----105----|------------------------------->
0 95 200
ID 3 4
将两组间隔放在一起(将它们相交):
Put both sets of intervals together (intersect them):
|--------|-|----------|------------------------------->
0 95 100 200
现在你有了间隔:
From To InvoiceID PaymentID
------------------------------------
0 95 3 3
95 100 3 4
100 200 4 4
<小时>
对于这些间隔中的每一个，最多可以有一张发票和最多一笔付款(也可以没有).找出对应于每个间隔的发票和付款，您就获得了发票和付款之间的映射.总结每张发票的所有付款间隔，您就会知道发票是全额付款还是部分付款.
For each of these intervals there can be at most one invoice and at most one payment (there can be none as well). Find which invoice and payment correspond to each of these intervals and you've got a mapping between your invoices and payments. Sum up all Payment intervals for each Invoice and you'll know whether invoice was paid in full or partially.
分别为发票和付款建立初始间隔列表是通过运行总计完成的.
Building initial list of intervals separately for Invoices and Payments is done by running total.
SUM(Amount) OVER (PARTITION BY ReceiverId ORDER BY Priority
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS InvoiceInterval
SUM(Amount) OVER (PARTITION BY SenderId ORDER BY PaymentID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS PaymentInterval
将这两个集合相交是一个简单的UNION.
Intersecting these two sets is a simple UNION.
对于每个间隔，找到相应的发票和付款.一种简单的方法是在 OUTER APPLY 中使用子查询.
For each interval find a corresponding Invoice and Payment. One simple way to do it is subquery in OUTER APPLY.
让我们把所有这些放在一起.
Let's put all this together.
示例数据
DECLARE @Invoice TABLE
(
[InvoiceId] [int] NOT NULL,
[ReceiverId] [int] NOT NULL,
[Amount] [numeric](19, 2) NOT NULL,
[Priority] [int] NOT NULL
);
DECLARE @Payment TABLE
(
[PaymentId] [int] NOT NULL,
[SenderId] [int] NOT NULL,
[Amount] [numeric](19, 2) NOT NULL
);
INSERT INTO @Invoice(InvoiceId,ReceiverId,Amount,Priority) VALUES
(1, 1, 100.00, 1),
(2, 1, 100.00, 2),
(3, 2, 100.00, 1),
(4, 2, 100.00, 2),
(5, 1, 200.00, 3);
INSERT INTO @Payment(PaymentId, SenderId, Amount) VALUES
(1, 1, 50.00),
(2, 1, 45.00),
(3, 2, 95.00),
(4, 2, 105.00);
查询
WITH
CTE_InvoiceIntervals
AS
(
SELECT
I.InvoiceId
,I.ReceiverId AS ClientID
,I.Priority
,SUM(I.Amount) OVER (PARTITION BY I.ReceiverId ORDER BY I.Priority
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS InvoiceInterval
FROM @Invoice AS I
)
,CTE_PaymentIntervals
AS
(
SELECT
P.PaymentId
,P.SenderId AS ClientID
,P.PaymentId AS Priority
,SUM(P.Amount) OVER (PARTITION BY P.SenderId ORDER BY P.PaymentID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS PaymentInterval
FROM @Payment AS P
)
,CTE_AllIntervals
AS
(
SELECT
ClientID
,InvoiceInterval AS Interval
FROM CTE_InvoiceIntervals
UNION
SELECT
ClientID
,PaymentInterval AS Interval
FROM CTE_PaymentIntervals
)
SELECT *
FROM
CTE_AllIntervals
OUTER APPLY
(
SELECT TOP(1) CTE_InvoiceIntervals.InvoiceId
FROM CTE_InvoiceIntervals
WHERE
CTE_InvoiceIntervals.ClientID = CTE_AllIntervals.ClientID
AND CTE_InvoiceIntervals.InvoiceInterval >= CTE_AllIntervals.Interval
ORDER BY
CTE_InvoiceIntervals.InvoiceInterval
) AS A_Invoices
OUTER APPLY
(
SELECT TOP(1) CTE_PaymentIntervals.PaymentId
FROM CTE_PaymentIntervals
WHERE
CTE_PaymentIntervals.ClientID = CTE_AllIntervals.ClientID
AND CTE_PaymentIntervals.PaymentInterval >= CTE_AllIntervals.Interval
ORDER BY
CTE_PaymentIntervals.PaymentInterval
) AS A_Payments
ORDER BY
ClientID
,Interval;
结果
+----------+----------+-----------+-----------+
| ClientID | Interval | InvoiceId | PaymentId |
+----------+----------+-----------+-----------+
| 1 | 50.00 | 1 | 1 |
| 1 | 95.00 | 1 | 2 |
| 1 | 100.00 | 1 | NULL |
| 1 | 200.00 | 2 | NULL |
| 1 | 400.00 | 5 | NULL |
| 2 | 95.00 | 3 | 3 |
| 2 | 100.00 | 3 | 4 |
| 2 | 200.00 | 4 | 4 |
+----------+----------+-----------+-----------+
这篇关于在 T-SQL 中执行运行减法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
