我有物料三個庫： 1 物料入庫 IN 段位如下： NO（物料編號） IN_A(物料入庫數量) IN_B(入庫日期) 2 物料出庫 OUT 段位如下： NO（物料編號） OUT_A(物料出庫數量) OUT_B(出庫日期) 3 物料庫存 A 段位如下： NO（物料編號） NAME(物料名稱) B(庫存數量) 我想以出入庫日期和物料編號為動態條件，作一個‘物料出.入.存對應表' 其中出入庫要按物料編號匯總，格式如下： 物料編號 。 物料名稱 。 纍計入庫數量 。 纍計出庫數量 。 現庫存 請教如何下一條SQL命令實現，萬分謝謝！！ 發表人 - zhczm 於 2004/02/03 19:38:40 發表人 - zhczm 於 2004/02/03 19:50:25

Hi:    你可以試試以下語法

select  a.no,
        a.name,
        sum(trx.in_a) as in_a,
        sum(trx.out_a) as out_a,
        a.b
from   (select  no,
                sum(in_a) as in_a,
                0 as out_a
        from    in
        where   in_b between :p_date1 and :p_date2
        group by
                no
        union all
        select  no,
                0 as in_a,
                sum(out_a) as out_a
        from    out
        where   out_b between :p_date1 and :p_date2
        group by
                no) trx,
        a
where   a.no = trx.no
and     a.no = :p_no
group by
        a.no,
        a.name,
        a.b

若是連沒有交易紀錄的資料也要含入，則使用

select  a.no,
        a.name,
        sum(trx.in_a) as in_a,
        sum(trx.out_a) as out_a,
        a.b
from   (select  no,
                sum(in_a) as in_a,
                0 as out_a
        from    in
        where   in_b between :p_date1 and :p_date2
        group by
                no
        union all
        select  no,
                0 as in_a,
                sum(out_a) as out_a
        from    out
        where   out_b between :p_date1 and :p_date2
        group by
                no
        union
        select  no
                0 as in_a
                0 as out_a
        from    a
        where   no = :p_no) trx,
        a a
where   a.no = trx.no
group by
        a.no,
        a.name,
        a.b

PS:以上語法接沒有實際測過，可能會有語法上的錯誤，若有誤請告知 -------------------------------- 小弟才疏學淺，若有謬誤請不吝指教 --------------------------------

------
Fishman

Hi:    Sorry，剛剛檢視一下第二個 Query，有些語法上的錯誤特此更正如下

select  a.no,
        a.name,
        sum(trx.in_a) as in_a,
        sum(trx.out_a) as out_a,
        a.b
from   (select  no,
                sum(in_a) as in_a,
                0 as out_a
        from    in
        where   in_b between :p_date1 and :p_date2
        group by
                no
        union all
        select  no,
                0 as in_a,
                sum(out_a) as out_a
        from    out
        where   out_b between :p_date1 and :p_date2
        group by
                no
        union
        select  no
                0 as in_a
                0 as out_a
        from    a) trx,
        a a
where   a.no = trx.no
and     a.no = :p_no
group by
        a.no,
        a.name,
        a.b

-------------------------------- 小弟才疏學淺，若有謬誤請不吝指教 --------------------------------

------
Fishman