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
LEFT JOIN t_store_apply_material m ON m.fID = t.main_id
LEFT JOIN (
SELECT sid.material_number, SUM(sid.count) AS sumincount,sid.batch_number
FROM t_central_stockout_detail sid
LEFT JOIN t_central_stockout si ON sid.main_id = si.fID
WHERE si.store_apply_number='a_20170823_2'
GROUP BY sid.material_number,sid.batch_number) d ON t.material_number = d.material_number AND t.batch_number = d.batch_number
WHERE m.apply_number = 'a_20170823_2' 
-- AND (t.count- ISNULL(d.sumincount,0)) > 0
ORDER BY t.apply_item_number ASC

用到的SQL函数有 ISNULL、SUM、GROUP BY 、 FULL JOIN

仓库物品批次与无批次汇总数量核实校验查询

SELECT *
FROM (
SELECT n.material_number AS mn, n.stock_count
FROM t_warehouse_material_nobatch n
WHERE n.warehouse_number='wh_nj_10001' AND n.material_number IN (
SELECT d.material_number
FROM t_store_stock_take_detail d
WHERE d.stock_take_number='st_20170731_9' AND d.diff_count!=0
) 
) a FULL JOIN (
SELECT b.material_number AS mn, SUM(b.count) AS ct
FROM t_warehouse_material b
WHERE b.warehouse_number='wh_nj_10001' AND b.material_number IN (
SELECT d.material_number
FROM t_store_stock_take_detail d
WHERE d.stock_take_number='st_20170731_9' AND d.diff_count!=0
)
GROUP BY b.material_number) c ON a.mn=c.mn
ORDER BY c.mn ASC 

原文链接：https://blog.csdn.net/xuanjiewu/article/details/77533087

本站声明:网站内容来源于网络,如有侵权,请联系我们,我们将及时处理。