近期需要合併兩個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.銷貨日期
沒有留言:
張貼留言