Re: [請益] 如何做到好幾個資料表的資料聯集合併?
看板PHP作者yuleen123 (.......................)時間16年前 (2009/09/09 13:29)推噓1(1推 0噓 2→)留言3則, 1人參與討論串2/2 (看更多)
這個有點麻煩
我用 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
推
09/09 22:47, , 1F
09/09 22:47, 1F
→
09/09 22:48, , 2F
09/09 22:48, 2F
→
09/09 22:49, , 3F
09/09 22:49, 3F
討論串 (同標題文章)
完整討論串 (本文為第 2 之 2 篇)：
PHP 近期熱門文章
PTT數位生活區 即時熱門文章
