已知商品表SpInfo 如圖
出入庫主表SpCRK 如圖：
出入庫從表SpCRK2 如圖：
SpInfo.SpID=SpCRK2.SpCode 和SpCRK.BillCode=SpCRK2.BillCode SpCRK.BillType(1代表出庫，2代表入庫)
SpCRK2.SpQty 為商品出入庫數量，求商品的庫存。
uj5u.com熱心網友回復：
終于解決了。uj5u.com熱心網友回復：
解決了就好了。祝賀，接分。uj5u.com熱心網友回復：
樓主是用SQL陳述句還是Delphi代碼來實作呢？uj5u.com熱心網友回復：
有bug 當只有出庫無入庫時，沒法顯示 ，或者有入庫無出庫時也沒法顯示，大家幫我看看
DM.SpCXQuery.SQL.Add('select SpID,SumRK-SumCK as SpQty from (SpInfo A inner join(');
DM.SpCXQuery.SQL.Add('select SpCode,Sum(SpQty) as SumRK from SpCRK2 Where BillCode in(');
DM.SpCXQuery.SQL.Add('select BillCode from SpCRK where BillType=2 ) group by SpCode) B on A.SpID=B.SpCode) inner');
DM.SpCXQuery.SQL.Add('join (select SpCode,Sum(SpQty) as SumCK from SpCRK2 Where BillCode in(select BillCode from SpCRK where BillType=1 ) group by SpCode) C on A.SpID=C.SpCode');
uj5u.com熱心網友回復：
有bug 當只有出庫無入庫時，沒法顯示 ，或者有入庫無出庫時也沒法顯示，大家幫我看看
DM.SpCXQuery.SQL.Add('select SpID,SumRK-SumCK as SpQty from (SpInfo A inner join(');
DM.SpCXQuery.SQL.Add('select SpCode,Sum(SpQty) as SumRK from SpCRK2 Where BillCode in(');
DM.SpCXQuery.SQL.Add('select BillCode from SpCRK where BillType=2 ) group by SpCode) B on A.SpID=B.SpCode) inner');
DM.SpCXQuery.SQL.Add('join (select SpCode,Sum(SpQty) as SumCK from SpCRK2 Where BillCode in(select BillCode from SpCRK where BillType=1 ) group by SpCode) C on A.SpID=C.SpCode');
uj5u.com熱心網友回復：
最好在資料庫里面處理，寫進去幾個引數來計算庫存資訊。uj5u.com熱心網友回復：
大致這個寫法：select a.SpID, isnull(SpQty2,0)-isnull(SpQty1,0) SpQty from SpInfo a
left join
(select SpCode, sum(SpQty) SpQty2 from SpCRK2 where BillType=2 group by SpCode)b
on a.SpID=b.SpCode
left join
(select SpCode, sum(SpQty) SpQty1 from SpCRK2 where BillType=1 group by SpCode)c
on a.SpID=c.SpCode
uj5u.com熱心網友回復：
select spid,spname,isnull((select sum(spqty) from SpCRK2,SpCRK where SpCRK2.billcode=SpCRK.billcode and BillType=2 and spcode=a.spid),0)-
isnull((select sum(spqty) from SpCRK2,SpCRK where SpCRK2.billcode=SpCRK.billcode and BillType=1 and spcode=a.spid),0) as kc
from spinfo a
這樣寫就不存在你說的問題了。但是效率不知道怎么樣！你試試大資料量！反正我一般是這樣用的！
uj5u.com熱心網友回復：
樓主的控制元件好漂亮呀，是dbgrideh不，這顏色如何設定的呀轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/86050.html
標籤：數據庫相關
下一篇：adoquery的sort問題