SQL 语句关联函数查询进销存多次入库剩余数量统计

进销存多次入库剩余数量统计"/>

SQL 语句关联函数查询进销存多次入库剩余数量统计

SQL 语句关联函数查询进销存多次入库剩余数量统计，一个采购订单可以多次入库，每次入库时需统计之前该采购订单的每个物品已经入库了多少数量，还剩余多少数量需要入库，录入入库数量时需进行校验，不能大于剩余入库数量，

无批次

SELECT t.material_number, t.count, ISNULL(d.sumincount,0) AS sumincount, (t.count- ISNULL(d.sumincount,0)) AS diffCount
FROM t_store_apply_detail t
LEFT JOIN (
SELECT sid.material_number, SUM(sid.count) AS sumincount
FROM t_store_stockin_detail sid
LEFT JOIN t_store_stockin si ON sid.main_id = si.fID
WHERE si.store_apply_number='a_20170824_1'
GROUP BY sid.material_number) d ON t.material_number = d.material_number
WHERE t.store_apply_number = 'a_20170824_1'
-- and (t.count-isnull(d.sumincount,0)) > 0
ORDER BY t.apply_item_number ASC

带库存批次查询

SELECT t.material_number, t.count, ISNULL(d.sumincount,0) AS sumincount, (t.count- ISNULL(d.sumincount,0)) AS diffCount
FROM t_store_apply_detail t