Sql 如何实现同一列的递归计算

一种基于集合的解决方案，它考虑了非顺序标识符并取消了对递归级别的限制.

with
  t1 as (
    select *
    from (
      values
        (1, NULL, NULL, 11, NULL),
        (2, 'Sale', -1, 10, 100),
        (3, 'Purchase', 2, 12, 102),
        (4, 'Sale', -2, 10, 103)
    ) as t(id, "Transaction", amount, inventory, price)
  ),
  t2 as (
    select
      *,
      lead(id) over(order by id) as next_id
    from t1
  ),
  r as (
    select *, 90 as average
    from t2
    where "Transaction" is null
    union all
    select
      t2.*,
      case t2."Transaction"
        when 'Sale' then r.average
        else (r.Average * (t2.Inventory - t2.Amount) +
              t2.Amount * t2.Price) / t2.Inventory
      end
    from r
    join t2
    on t2.id = r.next_id
  )
select
  id, "Transaction", amount, inventory, price, average
from r
OPTION (MAXRECURSION 0)
;

db<>fiddle个

用于与基于集合的解决方案进行比较的基于指针的解决方案.

drop table if exists #data;

create table #data (
  id int,
  "Transaction" nvarchar(10),
  amount int,
  inventory int,
  price int
);

insert into #data(id, "Transaction", amount, inventory, price)
  values
    (1, NULL, NULL, 11, NULL),
    (2, 'Sale', -1, 10, 100),
    (3, 'Purchase', 2, 12, 102),
    (4, 'Sale', -2, 10, 103);

drop table if exists #averages;

create table #averages (
  id int,
  "Transaction" nvarchar(10),
  amount int,
  inventory int,
  price int,
  average float
);

declare
  @id int,
  @tran nvarchar(10),
  @amt int,
  @inv int,
  @price int,
  @avg float;

DECLARE C CURSOR FORWARD_ONLY STATIC READ_ONLY FOR
  select *
  from #data
  order by id;

open c;

FETCH NEXT FROM C INTO @id, @tran, @amt, @inv, @price;

WHILE @@FETCH_STATUS = 0 BEGIN
  set @avg = case
               when @tran is null then 90
               else
                 case @tran
                   when 'Sale' then @avg
                   else (@avg * (@inv - @amt) +
                         @amt * @price) / @inv
                 end
             end;
  insert into #averages(id, "Transaction", amount, inventory, price, average)
    values(@id, @tran, @amt, @inv, @price, @avg);
  FETCH NEXT FROM C INTO @id, @tran, @amt, @inv, @price;
END;

CLOSE C;
DEALLOCATE C;

select * from #averages;

https://dbfiddle.uk/m3nePu26个

这两种解决方案的结果:

+----+-------------+--------+-----------+-------+---------+
| id | Transaction | amount | inventory | price | average |
+----+-------------+--------+-----------+-------+---------+
|  1 | null        | null   |        11 | null  |      90 |
|  2 | Sale        | -1     |        10 | 100   |      90 |
|  3 | Purchase    | 2      |        12 | 102   |      92 |
|  4 | Sale        | -2     |        10 | 103   |      92 |
+----+-------------+--------+-----------+-------+---------+