我需要從四個select擷取資料，形成一個新的資料集合

select plu_no,plu_name,firstqty as 期初數量
from firststocks
select 商品代碼,商品名稱,sum(數量) as 進貨數量
from jin_huo_detail
where 數量>0
group by 商品代碼,商品名稱
select 商品代碼,商品名稱,sum(數量) as 進貨退回
from jin_huo_detail
where 數量<0
group by 商品代碼,商品名稱
select purchdetail.plu_no,plu.plu_name,sum(qty) as 銷貨數量
from purchdetail,plu
where qty>0 and purchdetail.plu_no=plu.plu_no
group by purchdetail.plu_no,plu.plu_name
select purchdetail.plu_no,plu.plu_name,sum(qty) as 銷貨退回
from purchdetail,plu
where qty<0 and purchdetail.plu_no=plu.plu_no
group by purchdetail.plu_no,plu.plu_name



以上是貨品的期初數量、進貨數量、進貨退回數量、銷貨數量、銷貨退回

我如何把它們變成一個資料集合=>期初數量 進貨數量-進貨退回-銷貨數量 銷貨退回=期末數量?

編輯記錄

select 商品代碼,商品名稱,sum(期初數量) 期初數量,sum(進貨數量)  進貨數量,sum(進貨退回) 進貨退回,sum(銷貨數量) 銷貨數量
from
(
select plu_no as 商品代碼,plu_name as 商品名稱,firstqty as 期初數量,0 as 進貨數量,0 as 進貨退回,0 as 銷貨數量
from firststocks
union
select 商品代碼,商品名稱,0 as 期初數量,數量 as 進貨數量,0 as 進貨退回,0 as 銷貨數量
from jin_huo_detail
where 數量>0
union
select 商品代碼,商品名稱,0 as 期初數量,0 as 進貨數量,數量 as 進貨退回,0 as 銷貨數量
from jin_huo_detail
where 數量<0
union
select purchdetail.plu_no as 商品代碼 ,plu.plu_name as 商品名稱,0 as 期初數量,0 as 進貨數量,0 as 進貨退回,qty as 銷貨數量
from purchdetail a join plu b on a.plu_no=b.plu_no
where qty>0
) anyName
group by 商品代碼,商品名稱
===================引 用 littlecheng 文 章===================
我需要從四個select擷取資料，形成一個新的資料集合

select plu_no,plu_name,firstqty as 期初數量
from firststocks
select 商品代碼,商品名稱,sum(數量) as 進貨數量
from jin_huo_detail
where 數量>0
group by 商品代碼,商品名稱
select 商品代碼,商品名稱,sum(數量) as 進貨退回
from jin_huo_detail
where 數量<0
group by 商品代碼,商品名稱
select purchdetail.plu_no,plu.plu_name,sum(qty) as 銷貨數量
from purchdetail,plu
where qty>0 and purchdetail.plu_no=plu.plu_no
group by purchdetail.plu_no,plu.plu_name
select purchdetail.plu_no,plu.plu_name,sum(qty) as 銷貨退回
from purchdetail,plu
where qty<0 and purchdetail.plu_no=plu.plu_no
group by purchdetail.plu_no,plu.plu_name



以上是貨品的期初數量、進貨數量、進貨退回數量、銷貨數量、銷貨退回

我如何把它們變成一個資料集合=>期初數量 進貨數量-進貨退回-銷貨數量 銷貨退回=期末數量?

編輯記錄