[SQL] SQL은 - 행에서 파괴 값을 빼면

SQL은 - 행에서 파괴 값을 빼면

내가 한 테이블에서 "양이 소비"가지고 가고, 수량의 "풀을 많이"있는 1 개 이상의 행이 두 번째 테이블에 적용해야 할 상황이있다. 나는 여기에 내가 테이블의 관점에서 의미하는 무엇을, 더 잘 설명하는 방법을 잘 모르겠어요 :

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 쿼리에서 결과 행 집합이 필요합니다 :

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 어디 Pool_Consumption.PoolId = Pooled_Lots.Pool Pooled_Lots에서 행에 걸쳐 차감는 "파괴 값"할 필요가있다. 난 당신이 말한다 쿼리를 명시 할 방법을 알아낼 수 없습니다 :

ID가 기본 키라고 가정하고 대상 DB는 SQL 2005입니다.

편집 : 사람들이 내가 선포하고 있기 때문에 "충분한 정보를 제공하지, 주변이 제발은"여기 더 다음 Pool_Consumption 때까지, 모든 많은 어디에서 Pool_Consumption.PoolId = Pooled_Lots.Pool을 그릴 필요가에서 그립니다 NO 세트 많이있다 QuantityConsumed 중 하나를 완전히 고갈 또는 내가 Pooled_Lots 행의 마지막 부분 집합에 대해 공제하고 어디 Pool_Consumption.PoolId = Pooled_Lots.Pool

나는 이것을 설명하는 방법을 더 모른다. 이것은이가 만들어 낸 "생각 운동"아닙니다, 숙제 질문이 아니다. 나는 제대로 여러 행에 대해 QuantityConsumed을 빼기하는 방법을 알아 내려고 도움이 필요합니다!

해결법

1. ==============================

   1.영업에 연습으로 왼쪽 : 샘플 데이터 주어진 올바른 결과를 알아내는 다음과 같은 쿼리의 결과를 요약 :

   영업에 연습으로 왼쪽 : 샘플 데이터 주어진 올바른 결과를 알아내는 다음과 같은 쿼리의 결과를 요약 :

   -- 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
   

2. ==============================

   2.(내 와이파이가 꽤 많은 시간 동안 내려 갔다으로 질문의 버전 4 기준)

   (내 와이파이가 꽤 많은 시간 동안 내려 갔다으로 질문의 버전 4 기준)

   (SELECT
     Pool,
     SUM(Quantity) as Pool_Quantity
   FROM
     Pooled_Lots
   GROUP BY
     Pool) as Pool_Quantity_Table
   

   지금 당신은 하나의 값으로 롤업 풀 수량이있는 테이블이있다.

   이제 전체 쿼리 :

   SELECT
     Pool_Consumption.PoolID as Pool,
     Pool_Quantity_Table.Pool_Quantity as Quantity,
     Pool_Consumption.QuantityConsumed as AmtConsumedFromLot,
     (Pool_Quantity_Table.Pool_Quantity - Pool_Consumption.QuantityConsumed) as SurplusOrDefecit
   FROM
     Pool_Consumption
   INNER JOIN
     (SELECT
       Pool,
       SUM(Quantity) as Pool_Quantity
     FROM
       Pooled_Lots
     GROUP BY
       Pool) as Pool_Quantity_Table
   ON (Pool_Consumption.PoolID = Pool_Quantity_Table.Pool);
   

from https://stackoverflow.com/questions/9420173/sql-subtracting-a-depleting-value-from-rows by cc-by-sa and MIT license