select cut_ID, sum(Sqty) as RealQty
from (
select cut_id , qty as Sqty from outTable
union
select cut_id , -qty as Sqty from backTable
) as Agroup by cut_ID----以上為分CUT_ID分別匯總
----outTable 為出庫表,backTable為退庫表
----支持多次出庫多次退庫
from (
select cut_id , qty as Sqty from outTable
union
select cut_id , -qty as Sqty from backTable
) as Agroup by cut_ID----以上為分CUT_ID分別匯總
----outTable 為出庫表,backTable為退庫表
----支持多次出庫多次退庫
from (
select cut_id , qty as Sqty from outTable where cut_id = '1234'
union
select cut_id , -qty as Sqty from backTable where cut_id = '1234'
) as Agroup by cut_ID
无论是出库或退库都应该用两个表来表示!例如出库表可以分为ckdml(出库单)和ckdnr(出库单内容)。
ckdml的主键是Cut_ID
ckdnr的主键是Cut_ID和xh,其中xh表示序号,在此表中用来保存出库的详细信息。
这种方法也是目前大多数软件采用的方法。
from (
select cut_id , Out_Qty as Sqty from OutMaterial
union
select cut_id , -Ret_Qty as Sqty from ReturnFab
) as Agroup by cut_ID-----注意 as Sqty
from outtable a inner join intable b
on a.cut_ID=b.cut_ID
group by a.cut_ID
(ISNULL(SUM(OutMaterial.Out_Qty),0) -
ISNULL(SUM(ReturnFab.Ret_Qty),0)) AS Qty_SUM
FROM OutMaterial LEFT OUTER JOIN
Ret_Qty ON OutMaterial.Cut_ID = Ret_Qty.Cut_ID
GROUP BY OutMaterial.Cut_ID
可以试试
SELECT OutMaterial.Cut_ID,OutMaterial.proName
(ISNULL(SUM(OutMaterial.Out_Qty),0) -
ISNULL(SUM(ReturnFab.Ret_Qty),0)) AS Qty_SUM
FROM OutMaterial LEFT OUTER JOIN
Ret_Qty ON OutMaterial.Cut_ID = Ret_Qty.Cut_ID
GROUP BY OutMaterial.Cut_ID,OutMaterial.proName
如果物品名称字段不再OutMaterial中,可以再联合别的表统计便可以
from (
select cut_id , itemName ,Out_Qty as Sqty from OutMaterial
union
select cut_id , itemName ,-Ret_Qty as Sqty from ReturnFab
) as Agroup by cut_ID ,itemname