为网友解题:如何根据合同明细表及已发货明细表，来计算合同未发货的数量？

此问题的关键是，一个合同中可以有重复的产品，所以计算未发货的数量非常困难！
主要情况举例如下：
我有一个合同明细表，如：
合同编号　产品编号　合同数量
S1      A        100
S1　　　　A　　　　 200
S1　　　　B　　　　 150
S2　　　　C　　　　 500
...
还有一个出货明细表,如：
出货单号　合同编号　产品编号　出货数量
D1      S1      A        50
D1      S1      B        100
D2      S2      A        120
......

用SQL怎样才能计算表来：
未送货的合同明细表
合同编号　产品编号　合同数量  已送货数量  未送货数量
S1      A        100          100          0
S1　　　　A　　　　200          70          130
S1　　　　B　　　　　150        100          50
S2　　　　C　　　　500          0            500

 

--------------------------------------------------------------------------

create table #t1(合同编号 varchar(20), 产品编号 varchar(10), 合同数量  int)
insert #t1 select 'S1','A',100
union   all       select 'S1','A',200
union   all       select  'S1','B',150
union   all       select 'S2','C',500
union   all       select 'S1','A',300
union   all       select 'S1','A',300

create table #t2(出货单号 varchar(10), 合同编号 varchar(10), 产品编号 varchar(10), 出货数量 int)
insert  #t2
select 'D1'   ,   'S1'   ,   'A'   ,     50 union all
select 'D1'    ,  'S1'   ,   'B'   ,     100 union all
select 'D1'   ,   'S1'   ,   'A'   ,     130 union all
select 'D2'    ,  'S2'   ,   'A'    ,    125

select id=identity(int,1,1),* into #t3 from #t1 order by 产品编号

--select * from #t3

select
  a.合同编号,
  a.产品编号,
  a.合同数量,
  已送货数量 = case when a.合同数量 <= isnull(b.出货数量-( (select sum(合同数量) from #t3 where 产品编号=a.产品编号 and id<=a.id)-a.合同数量),0)
                       then a.合同数量
                   when a.合同数量 > isnull(b.出货数量-( (select sum(合同数量) from #t3 where 产品编号=a.产品编号 and id<=a.id)-a.合同数量),0)
                       then
                        case when isnull(b.出货数量-( (select sum(合同数量) from #t3 where 产品编号=a.产品编号 and id<=a.id)-a.合同数量),0) >= 0
                               then isnull(b.出货数量-( (select sum(合同数量) from #t3 where 产品编号=a.产品编号 and id<=a.id)-a.合同数量),0)
                              else 0
                        end 
                   end,

  未送货数量 = a.合同数量 -  case when a.合同数量 <= isnull(b.出货数量-( (select sum(合同数量) from #t3 where 产品编号=a.产品编号 and id<=a.id)-a.合同数量),0)
                       then a.合同数量
                   when a.合同数量 > isnull(b.出货数量-( (select sum(合同数量) from #t3 where 产品编号=a.产品编号 and id<=a.id)-a.合同数量),0)
                       then
                        case when isnull(b.出货数量-( (select sum(合同数量) from #t3 where 产品编号=a.产品编号 and id<=a.id)-a.合同数量),0) >= 0
                               then isnull(b.出货数量-( (select sum(合同数量) from #t3 where 产品编号=a.产品编号 and id<=a.id)-a.合同数量),0)
                              else 0
                        end 
                   end
from #t3 a
   left join (select 产品编号,sum(isnull(出货数量,0)) 出货数量 from #t2 group by 产品编号) b on   a.产品编号=b.产品编号
   --left join (select 产品编号,sum(合同数量) 合同总数 from #t1 group by 产品编号) c on a.产品编号=c.产品编号


drop table #t1,#t2,#t3

----------------------------------
合同编号　产品编号　合同数量  已送货数量  未送货数量
------  -------- -------  --------- ---------
S1    A     100      100         0
S1    A     200      200         0
S1    A     300      5         295
S1    A     300      0         300
S1    B     150      100         50
S2    C     500      0         500