T-SQL在没有游标的行之间分配值

I have a simple live-case scenario on the SQL Server 2014. I have a table with orders and their quanities:

我在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)

I have a value, for example 50, which i have to distribute between orders, based on their priority Iiterating through the orders

我有一个值,例如50,我必须根据他们的优先顺序通过订单在订单之间分配

if the amount left is greater then quanity of the current order, the order will be realized, and the @qty_to_distribute reduced by the qty of the order,

如果剩余金额大于当前订单的数量,则将实现订单,并且@qty_to_distribute减少订单的数量,

if not - the order is cancelled.

如果不是 - 订单被取消。

Looking at the sample data - the orders 1, 2 and 4 will be realized and #3 cancelled.

查看样本数据 - 将实现订单1,2和4,并取消#3。

Is there a simple, effective and non-cursor solution, preferably based on window functions?

是否有一个简单,有效和非游标的解决方案,最好是基于窗口函数?

This looks promising, but can't figure out where to put the conditions:

这看起来很有希望,但无法弄清楚在哪里放置条件:

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

2 个解决方案

#1

Try this:

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)

Output:

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

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

#2

UPDATED thanks to Giorgi Nakeuri

更新感谢Giorgi Nakeuri

WITH Intermediate AS (
  SELECT 
      Id, 
      ROW_NUMBER() OVER (ORDER BY Priority) as Priority, 
      qty                
  FROM Orders),
Result AS
(
    SELECT 
        Id, 
        Priority,
        CASE when qty < 50 THEN 0 ELSE qty END as Residual,
        CASE when qty < 50 THEN 50 - qty ELSE 50 END as Remaining   
    FROM Intermediate
    WHERE Priority = 1
    UNION ALL  
    SELECT  
         ORD.Id, 
         ORD.Priority, 
         CASE when ORD.qty < Result.Remaining THEN 0 ELSE qty END as Residual,
         CASE when ORD.qty < Result.Remaining THEN Result.Remaining - ORD.qty ELSE Result.Remaining END as Remaining
    FROM Intermediate ORD INNER JOIN Result ON ORD.Priority = Result.Priority + 1
  )
  select Id, Priority, Residual, Remaining from result

http://sqlfiddle.com/#!6/2ac98f/2

#1