編輯記錄

以個人經驗, 這種情形要用 join  來處理的話常有沒想到的情況發生(例如 proda 無期初值, prodb 無進貨, prodc 無銷貨等等)
所以大都以 union all 來處理, 效率可能不好, 但比較不會漏東漏西, 參考一下 ( 假設沒有期初檔, 期初值由進貨-銷貨算出 ),

select prod_id, sum(bgnqty) 期初, sum(buyqty) 進貨, sum(selqty) 銷貨, sum(bgnqty) sum(buyqty) - sum(selqty) 期末
from (
-- 期初
select prod_id, sum(buyqty - selqty) bgnqty, 0 buyqty, 0 selqty
from (
select prod_id, buyqty, 0 selqty from table_buy where tran_date<'20080901'
union all
select prod_id, 0, selqty from table_sel where tran_date<'20080901'
)
group by prod_id
union all
-- 進貨
select prod_id, 0 bgnqty, buyqty, 0 selqty
from table_buy
where tran_date between '20080901' and '20080930'
union all
-- 銷貨
select prod_id, 0 bgnqty, 0 buyqty, selqty
from table_sel
where tran_date between '20080901' and '20080930'
)
group by prod_id
------------------------------------------------------------
如果有一個完整的基本檔(商品資料檔), 也可以 基本檔 為主, 其餘的 left join 進來
select prod_id, bgnqty 期初, buyqty 進貨, selqty 銷貨, bgnqty buyqty - selqty 期末
from table_prod, (
-- 期初
select prod_id, sum(buyqty - selqty) bgnqty
from (
select prod_id, buyqty, 0 selqty from table_buy where tran_date<'20080901'
union all
select prod_id, 0, selqty from table_sel where tran_date<'20080901'
)
group by prod_id
) table_bgn, (
-- 進貨
select prod_id, sum(buyqty) buyqty
from table_buy
where tran_date between '20080901' and '20080930'
group by prod_id
) table_buy, (
-- 銷貨
select prod_id, sum(selqty) selqty
from table_sel
where tran_date between '20080901' and '20080930'
group by prod_id
) table_sel
where table_prod.prod_id = table_bgn.prod_id( )
and table_prod.prod_id = table_buy.prod_id( )
and table_prod.prod_id = table_sel.prod_id( )


===================引 用 littlecheng 文 章===================

------
IS IT WHAT IT IS
我是 李慕白 請倒著唸.
又想把老話拿出來說, 請用台語發音 : 專家專家全是ROBOT CAR (滷肉腳啦);
都已接手這麼久了, 績效還是那麼爛, 講話還那麼大聲.

編輯記錄

謝謝指導，在下受教了。