推 jami520 :我會再試試看，當初想說好玩，所以幫朋友寫寫看 09/09 22:47
→ jami520 :想了很久還是有點繞不出來，這版上高手如雲，很多值 09/09 22:48
→ jami520 :得學習的，謝謝! 09/09 22:49
這個有點麻煩
我用 MySQL 測試, 包兩層 SQL
select
pno,
pname,
customer,
type,
cdate,
sum(cnum),
(select total from a where a.pno=tmp.pno) + ifnull( (select sum(Pnum) from
c where c.Pdate <= tmp.cdate and c.pno=tmp.pno ),0) - ifnull( (select
sum(Onum) from b where b.Odate <= tmp.cdate and b.pno=tmp.pno), 0) as tnum
from
(
select
a.pno as pno,
a.pname as pname,
b.customer as customer,
'訂購' as type,
b.Odate as cdate,
b.Onum as cnum
from a inner join b on a.pno=b.pno
union all
select
a.pno as pno,
a.pname as pname,
'總公司' as customer,
'採購' as type,
c.Pdate as cdate,
c.Pnum as cnum
from a inner join c on a.pno=c.pno
) as tmp
group by tmp.pno, tmp.pname, tmp.customer, tmp.cdate
order by tmp.pno, tmp.cdate
查詢結果
pno pname customer type cdate cnum tnum
--------------------------------------------------------------------
A1234 白色包包 小馬 訂購 2009-08-22 4 -1
A1234 白色包包 總公司 採購 2009-08-25 2 1
A1234 白色包包 小明 訂購 2009-09-01 2 -1
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 118.171.135.37