根据一个表中产品的数量来减少另一个表中的产品数量

[orcd]

两个表
table1 出库表
fbh fmc fsl
001 杯子 10
002 钢笔 5
003 手机 10
table2 库存表
fbh fmc fph fsl
001 杯子 0901 5
001 杯子 0902 25
002 钢笔 0906 10
002 钢笔 0907 10

根据出库表的内容来减少库存，在库存表中不存在的可以不用考虑 ，出库的时候应该先出批次靠前的，
上面的结果应该显示为
table2 库存表
fbh fmc fph fsl
001 杯子 0901 0
001 杯子 0902 20
002 钢笔 0906 5
002 钢笔 0907 10

[medical52001]

这种类型应该用触发器实现
参考下触发器的实现吧

另外，奇怪是，你怎么把出库和入库分两张表，这个应该是存在同一个数据表里面的
这样不符合标准范式呀！！！

[orcd]

就是想在触发器里面实现啊。我是用的一张表，只是为了好表达这个问题这样说的

[playwarcraft]

Create table T1(fbh varchar(03), fmc varchar(40), fsl int)
insert into T1 select '001','A',10
insert into T1 select '002','B',5
insert into T1 select '003','C',10

Create table T2(fbh varchar(03),fmc varchar(40),fph varchar(04),fsl int)
insert into T2 select '001','A','0901',5
insert into T2 select '001','A','0902',25
insert into T2 select '002','B','0906',10
insert into T2 select '002','B','0907',10
GO

Update T2 
set fsl = case when (select sum(fsl) from T2 a where a.fbh=T2.fbh and a.fmc=T2.fmc and a.fph<=T2.fph)<=isnull((select sum(fsl) from T1 where fbh=T2.fbh and fmc=T2.fmc),0)
                      then 0
              else  case when  isnull((select sum(fsl) from T2 a where a.fbh=T2.fbh and a.fmc=T2.fmc and a.fph<T2.fph),0)<=isnull((select sum(fsl) from T1 where fbh=T2.fbh and fmc=T2.fmc),0)
                      then (select sum(fsl) from T2 a where a.fbh=T2.fbh and a.fmc=T2.fmc and a.fph<=T2.fph) -isnull((select sum(fsl) from T1 where fbh=T2.fbh and fmc=T2.fmc),0)
                      else fsl  end
             end

select * from T2
/*
fbh  fmc                                      fph  fsl         
---- ---------------------------------------- ---- ----------- 
001  A                                        0901 0
001  A                                        0902 20
002  B                                        0906 5
002  B                                        0907 10
*/

drop table T1,T2

[等不到来世]

if object_id('[table1]') is not null drop table [table1]
go
create table [table1]([fbh] varchar(3),[fmc] varchar(4),[fsl] int)
insert [table1]
select '001','杯子',10 union all
select '002','钢笔',5 union all
select '003','手机',10
if object_id('[table2]') is not null drop table [table2]
go
create table [table2]([fbh] varchar(3),[fmc] varchar(4),[fph] varchar(4),[fsl] int)
insert [table2]
select '001','杯子','0901',5 union all
select '001','杯子','0902',25 union all
select '002','钢笔','0906',10 union all
select '002','钢笔','0907',10
go
--select * from [table1]
--select * from [table2]

update a
set fsl=case when a.fsl<=b.fsl-isnull((select sum(fsl) from table2 where fbh=a.fbh and fph<a.fph),0) then 0 
			when b.fsl>isnull((select sum(fsl) from table2 where fbh=a.fbh and fph<a.fph),0) then a.fsl-b.fsl+isnull((select sum(fsl) from table2 where fbh=a.fbh and fph<a.fph),0) 
			else a.fsl end
from table2 a,table1 b
where a.fbh=b.fbh

select * from [table2]
/*
fbh  fmc  fph  fsl
---- ---- ---- -----------
001  杯子   0901 0
001  杯子   0902 20
002  钢笔   0906 5
002  钢笔   0907 10

(4 行受影响)
*/

[csdyyr]

Create table T1(fbh varchar(03), fmc varchar(40), fsl int)
insert into T1 select '001','A',10
insert into T1 select '002','B',5
insert into T1 select '003','C',10

Create table T2(fbh varchar(03),fmc varchar(40),fph varchar(04),fsl int)
insert into T2 select '001','A','0901',5
insert into T2 select '001','A','0902',25
insert into T2 select '002','B','0906',10
insert into T2 select '002','B','0907',10
GO

update t2 set fsl=case when t.fsl>total then 0 else case when total2>t.fsl then t2.fsl else total-t.fsl end end 
from t2 join (
select t.fbh,t.fph,t1.fsl,total,total2
from t1 join (
select *,total=(select sum(fsl) from t2 where fbh=t.fbh and fph<=t.fph),
	total2=isnull((select sum(fsl) from t2 where fbh=t.fbh and fph<t.fph),0)
from t2 t)t
on t1.fbh=t.fbh
) t
on t2.fbh=t.fbh and t2.fph=t.fph

select * from t2

drop table t1,t2
/*
fbh  fmc                                      fph  fsl
---- ---------------------------------------- ---- -----------
001  A                                        0901 0
001  A                                        0902 20
002  B                                        0906 5
002  B                                        0907 10

(4 row(s) affected)
*/