2014年1月22日 星期三

合併兩個SELECT

近期需要合併兩個SELECT出來的資料,並加以統計,示意的範例如下:



Select Stock.銷貨日期, Stock.銷貨單號, StockDet.銷貨數量
From Stock inner join StockDet
Where Stock.No = StockDet.No AND 銷貨日期 Between '20131201' And '20131231'

Select Order.訂單日期, Order.訂單單號, OrderDer.訂單數量
From Order inner join OrderDet
Where Order.No = OrderDet.No AND 訂單日期 Between '20131201' And '20131231'

合併後要以兩個Select日期區間 20131201~20131231,顯示資料欄位:
 日期      銷貨數量    訂單數量
----------------------------------
20131201    1000         2000
20131202     700          500
20131203    2000         2000

語法為:
Select k.銷貨日期, sum(k.銷貨數量) as 銷貨數, sum(k.訂單數量) as 訂單數
From
(
Select Stock.銷貨日期, Stock.銷貨單號, StockDet.銷貨數量,NULL
From Stock inner join StockDet
Where Stock.No = StockDet.No AND 銷貨日期 Between '20131201' And '20131231'
UNION
Select Order.訂單日期, Order.訂單單號, NULL, OrderDer.訂單數量
From Order inner join OrderDet
Where Order.No = OrderDet.No AND 訂單日期 Between '20131201' And '20131231'
) as k
where k.銷貨日期 between '20131201' and '20131231'
Group by k.銷貨日期

沒有留言:

張貼留言