SQL运行总数到x

Question

我正在使用SQL Server 2019。我有一个包含发票的表，其中有5行。

+---------+---------+
| invoice |  amount |
+---------+---------+
| ABC     |     100 |
| DEF     |     125 |
| GHI     |     150 |
| JKL     |     160 |
| MNO     |     170 |

我想返回得到总计235的每一行，以及它的偏导数。发票GHI包含剩余部分金额10(235 - 100 - 125)。

+---------+---------+
| invoice |  amount |
+---------+---------+
| ABC     |     100 |
| DEF     |     125 |
| GHI     |      10 |
+---------+---------+

因为只有这3行才能把它发送到请求的235。我在想一个用户定义的函数，返回一个表，但我似乎不能拿出任何东西来处理运行总数与最后一个的部分。

任何想法吗?提前谢谢。

Answer 1

好吧，那么从你的帖子中我仍然不清楚汇总发票背后的原因，但是这里是我的解决方案。

首先，让我们创建一个包含5行发票的样例表。

create table dbo.invoices
(
invoice char(10),
amount decimal(10,2)
);
insert into dbo.invoices
values ('ABC', 100), ('DEF', 125), ('GHI', 150), ('JKL', 160), ('MNO', 170);

使用窗口函数，我们可以检查我们是否达到了目标数量:

declare @target_amount decimal(10,2) = 235;
with input as (
select invoice
, amount
, running_total = sum(amount) over(order by invoice asc)
, target_amount = @target_amount
from dbo.invoices
), net_amount as (
select *
, net_amount = target_amount - running_total
from input
), selection as (
select *
, new_amount = case when net_amount > 0 then amount else amount + net_amount end
, invoice_filter = case when lag(net_amount) over( order by invoice) < 0 then 0 else 1 end
from net_amount
) -- end result
select invoice
, original_amount = amount
, new_amount
from selection
where invoice_filter = 1

结果如下:

+---------+-----------------+------------+
| invoice | original_amount | new_amount |
+---------+-----------------+------------+
| ABC     |          100.00 |     100.00 |
| DEF     |          125.00 |     125.00 |
| GHI     |          150.00 |      10.00 |
+---------+-----------------+------------+