订单表
产品ID,订货量
1 80
2 100
3 300
4 200
*/
if exists (select * from sysobjects where object_id('库存表')=id and objectproperty(id,'isUserTable')=1)
drop table 库存表
if exists (select * from sysobjects where object_id('订单表')=id and objectproperty(id,'isUserTable')=1)
drop table 订单表
go
--方法一(适合订单表产品ID没有重复)
select a.产品ID,订货量-库存量 进货量 from 订单表 a join 库存表 b on a.产品ID=b.产品ID where 库存量-订货量<0
/*
产品ID,进货量
3 200
4 100
*/
--方法二(适合所有情况)
select 产品ID,-sum(库存量) 进货量 from
(
select 产品ID,库存量 from 库存表 union all
select 产品ID,-订货量 from 订单表
)a group by 产品ID having sum(库存量)<0
/*
产品ID,进货量
3 200
4 100
*/
select dd.产品id, nz([dd].[订货量],0)-nz([kc].[库存量],0) as 进货量
into t3
from 订单表 as dd inner join 库存表 as kc on dd.产品id = kc.产品id
where nz([dd].[订货量],0)-nz([kc].[库存量],0)>0