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

时间:2021-08-28 09:31:26

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


6  

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


4  

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


6  

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


4  

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