SQL - 从行中减去一个耗尽的值

2022-01-31 00:00:00 sql sql-server-2005 tsql sql-server

我有一种情况，我需要从一个表中获取消耗的数量"，然后将其应用于第二个表，该表有 1 行或更多行是合并批次"的数量.我不知道如何更好地描述它，这是我从表格的角度来看的意思:

I have a situation where I need to take a "quantity consumed" from one table, and apply it against a second table that has 1 or more rows that are "pooled lots" of quantities. I'm not sure how to describe it better, here's what I mean from a table perspective: 

Table Pooled_Lots
----------------------------
Id  Pool    Lot Quantity
1   1       1   5
2   1       2   10
3   1       3   4
4   2       1   7
5   3       1   1
6   3       2   5

Table Pool_Consumption
----------------------------
Id  PoolId  QuantityConsumed
1   1       17
2   2       8
3   3       10

我需要一个来自 SQL 查询的结果行集，如下所示:

I need a resulting rowset from a SQL query that would look like:

Pool    Lot Quantity    QuantityConsumed    RunningQuantity RemainingDemand SurplusOrDeficit
1       1   5           17                  0               12              NULL
1       2   10          17                  0               2               NULL
1       3   4           17                  2               0               2
2       1   7           8                   0               1               -1
3       1   1           10                  0               9               NULL
3       2   5           10                  0               4               -4

因此，Pool_Consumption.QuantityConsumed 需要是从 Pooled_Lots 的行中减去的消耗值"，其中 Pool_Consumption.PoolId = Pooled_Lots.Pool.我不知道你会如何陈述这样的查询:

So, Pool_Consumption.QuantityConsumed needs to be a "depleting value" subtracted over the rows from Pooled_Lots where Pool_Consumption.PoolId = Pooled_Lots.Pool. I can't figure out how you would state a query that says:

  • 如果不在最后一行，AmtConsumedFromLot = Quantity - QuantityConsumed if QuantityConsumed <数量，否则数量
  • 如果有更多行，QuantityConsumed = QuantityConsumed - Quantity
  • 循环到最后一行
  • 如果最后一行，AmtConsumedFromLot = QuantityConsumed

假设 Id 是主键，目标 DB 是 SQL 2005.

Assume Id is a primary key, and the target DB is SQL 2005.

由于人们宣称我没有提供足够的信息，请关闭此"这里还有更多: Pool_Consumption 从中提取的 NO 设置批次，它需要从 all Pool_Consumption.PoolId = Pooled_Lots.Pool 的批次，直到 QuantityConsumed 完全耗尽或我减去 Pooled_Lots 行的最后一个子集，其中 Pool_Consumption.PoolId = Pooled_Lots.Pool

Since people are proclaiming I am "not giving enough information, please close this" Here is more: There is NO set lot that the Pool_Consumption draws from, it needs to draw from all lots where Pool_Consumption.PoolId = Pooled_Lots.Pool, until QuantityConsumed is either completely depleted or I am subtracting against the last subset of Pooled_Lots rows where Pool_Consumption.PoolId = Pooled_Lots.Pool

我不知道该怎么解释.这不是一个家庭作业问题，这不是一个虚构的思想练习".我需要帮助来弄清楚如何正确减去多行的 QuantityConsumed！

I don't know how more to explain this. This is not a homework question, this is not a made-up "thought exercise". I need help trying to figure out how to properly subtract QuantityConsumed against multiple rows!

推荐答案

作为练习留给 OP:根据样本数据找出正确的结果并总结以下查询的结果:

Left as an exercise to the OP: Figuring out the correct results given the sample data and summarizing the results of the following query:

-- Create some test data.
declare @Pooled_Lots as table ( Id int, Pool int, Lot int, Quantity int );
insert into @Pooled_Lots ( Id, Pool, Lot, Quantity ) values
  ( 1, 1, 1, 5 ), ( 2, 1, 2, 10 ), ( 3, 1, 3, 4 ),
  ( 4, 2, 1, 7 ),
  ( 5, 3, 1, 1 ), ( 6, 3, 2, 5 );
declare @Pool_Consumption as table ( Id int, Pool int, QuantityConsumed int );
insert into @Pool_Consumption ( Id, Pool, QuantityConsumed ) values
  ( 1, 1, 17 ), ( 2, 2, 8 ), ( 3, 3, 10 );

select * from @Pooled_Lots order by Pool, Lot;
select * from @Pool_Consumption order by Pool;

with Amos as (
  -- Start with Lot 1 for each Pool.
  select PL.Pool, PL.Lot, PL.Quantity, PC.QuantityConsumed,
    case
      when PC.QuantityConsumed is NULL then PL.Quantity
      when PL.Quantity >= PC.QuantityConsumed then PL.Quantity - PC.QuantityConsumed
      when PL.Quantity < PC.QuantityConsumed then 0
      end as RunningQuantity,
    case
      when PC.QuantityConsumed is NULL then 0
      when PL.Quantity >= PC.QuantityConsumed then 0
      when PL.Quantity < PC.QuantityConsumed then PC.QuantityConsumed - PL.Quantity
      end as RemainingDemand
    from @Pooled_Lots as PL left outer join
      @Pool_Consumption as PC on PC.Pool = PL.Pool
    where Lot = 1
  union all
  -- Add the next Lot for each Pool.
  select PL.Pool, PL.Lot, PL.Quantity, CTE.QuantityConsumed,
    case
      when CTE.RunningQuantity + PL.Quantity >= CTE.RemainingDemand then CTE.RunningQuantity + PL.Quantity - CTE.RemainingDemand
      when CTE.RunningQuantity + PL.Quantity < CTE.RemainingDemand then 0
      end,
    case
      when CTE.RunningQuantity + PL.Quantity >= CTE.RemainingDemand then 0
      when CTE.RunningQuantity + PL.Quantity < CTE.RemainingDemand then CTE.RemainingDemand - CTE.RunningQuantity - PL.Quantity
      end
    from Amos as CTE inner join
      @Pooled_Lots as PL on PL.Pool = CTE.Pool and PL.Lot = CTE.Lot + 1
  )
select *,
  case
    when Lot = ( select max( Lot ) from @Pooled_Lots where Pool = Amos.Pool ) then RunningQuantity - RemainingDemand
    else NULL end as SurplusOrDeficit
  from Amos
  order by Pool, Lot;

相关文章