有商品表commodity:commodityId, commodityname ..
入库表Treasury: treasuryId,commodityId ,treasurysum(入库数量) treasurytime(入库时间)...
出库表liberary:liberaryId ,commodityId,liberarysum(出口数量) liberarysum(出口时间)...我要查某一时间段某商品的库存量 sql语句怎么写啊 ?
入库表Treasury: treasuryId,commodityId ,treasurysum(入库数量) treasurytime(入库时间)...
出库表liberary:liberaryId ,commodityId,liberarysum(出口数量) liberarysum(出口时间)...我要查某一时间段某商品的库存量 sql语句怎么写啊 ?
from(
select sum(treasurysum) as tr_qty from Treasury where commodityId=@某商品 and treasurytime<=@某一时间
union all
select -sum(liberarysum) from liberary where commodityId=@某商品 and liberarysum<=@某一时间
)
select a.commodityId,sum(COALESCE(a.treasurysum)-COALESCE(a.liberarysum))
) from commodity a
left join Treasury b on a.commodityId=b.commodityId
left join liberary c on a.commodityId=c.commodityId
group by a.commodityIdorselect a.*,a1.sto from commodity a
left join
(select commodityId,sum(treasurysum) as sto from
(select commodityId,treasurysum from Treasury
union all
select commodityId,-1*liberarysum from liberary
) a2 group by commodityId) a1
on a.commodityId=a1.commodityId