sql-server - T-SQL 在没有游标的情况下在行之间分配值

Question

我在 SQL Server 2014 上有一个简单的实际案例场景。我有一个包含订单及其数量的表:

declare @qty_to_distribute int = 50

create table orders (id int, priority int, qty int)

insert into orders (id, priority, qty) values
(1, 1, 10),
(2, 2, 30),
(3, 3, 20),
(4, 4, 5)

我有一个值，例如 50，我必须根据订单的优先级在订单之间分配该值遍历订单

• 如果剩余金额大于当前订单数量，则执行订单，@qty_to_distribute 减去订单数量，
• 如果没有 - 订单将被取消。

查看示例数据 - 订单 1、2 和 4 将被实现，#3 将被取消。

有没有一个简单的有效且非光标的解决方案，最好基于窗口函数？

这看起来很有希望，但不知道在哪里放置条件:

select id,
sum(qty) OVER (ORDER BY priority asc ROWS UNBOUNDED PRECEDING) as qtysum
from orders

id  qtysum
1   10
2   40
3   60      *
4   65      *

http://sqlfiddle.com/#!6/05fa2/2/0

Answer 1

试试这个:

create table o (id int, priority int, qty int)

insert into o (id, priority, qty) values
(1, 1, 10),
(2, 2, 30),
(3, 3, 20),
(4, 4, 5),
(5, 5, 1),
(6, 6, 10)

with cte1 as(select *, row_number() over(order by priority) as rn from o),
cte2 as(
  select top 1 id, priority, qty, rn,
  case when qty > 50 then 1 else 0 end as ToBeCanceled
  from cte1 order by rn
    Union all
  select cte1.id, cte1.priority, case when cte2.qty + cte1.qty > 50 then cte2.qty
  else cte2.qty + cte1.qty end as qty,
  cte1.rn,
  case when cte2.qty + cte1.qty > 50 then 1 else 0 end as ToBeCanceled 
  from cte1
  join cte2 on cte1.rn = cte2.rn + 1)
select id, priority, ToBeCanceled from cte2
option(maxrecursion 0)

输出:

id  ToBeCanceled
1   0
2   0
3   1
4   0
5   0
6   1

fiddle http://sqlfiddle.com/#!6/87ac9/19