本範例將FULL OUTER JOIN搭配COALESCE函數產生出貨調貨報表，以倉庫優先出貨(藍色)，如已無庫存，則至其他分店(紅色)調貨，圖示及SQL如下:
SQL
|
|
ORACLE
|
SELECT Inventory.*
,
Store.*
,
COALESCE(Inventory.Prod, Store.Prod) Prod
,
COALESCE(Inventory.Qty, Store.Qty) Qty
,
CASE WHEN Inventory.Prod IS NOT NULL THEN '倉庫' ELSE Store.Loc END Loc
FROM
(
--優先:
倉庫出貨
SELECT 'DDR2' Prod, 20 Qty
FROM DUAL
UNION ALL
SELECT 'DDR3' Prod, 100 Qty
FROM DUAL
UNION ALL
SELECT 'DDR4' Prod, 300 Qty
FROM DUAL
)
Inventory
FULL OUTER JOIN
(
--替代:
無現貨再由各分店調貨
SELECT 'DDR' Prod, 50 Qty, '光華店' Loc
FROM DUAL
UNION ALL
SELECT 'DDR2' Prod, 10 Qty, '公館店' Loc
FROM DUAL
UNION ALL
SELECT 'DDR4' Prod, 80 Qty, '站前店' Loc
FROM DUAL
)
Store
ON Inventory.Prod =
Store.Prod
ORDER BY COALESCE(Inventory.Prod,
Store.Prod)
-- 註：COALESCE可改用NVL |
MSSQL
|
SELECT *
, COALESCE(Warehouse.Prod, Store.Prod) Prod
, COALESCE(Warehouse.Qty, Store.Qty) Qty
, CASE WHEN Warehouse.Prod IS NOT NULL THEN '倉庫' ELSE Store.Loc END Loc
FROM --優先: 倉庫出貨
(VALUES ('DDR2', 20)
, ('DDR3', 100)
, ('DDR4', 300)
) Warehouse
(Prod, Qty)
FULL OUTER JOIN
--替代: 無現貨再由各分店調貨
(VALUES ('DDR', 50, '光華店')
, ('DDR2', 10, '公館店')
, ('DDR4', 80, '站前店')
) Store
(Prod, Qty, Loc)
ON Warehouse.Prod = Store.Prod
ORDER BY COALESCE(Warehouse.Prod, Store.Prod)
--註: COALESCE可改用ISNULL函數 |
1. FULL OUTER JOIN產生二者最大資料集
外部連結(Outer Join)則可根據定義的保留資料表(Preserved
table)為基礎，外部連結所連結的資料表中，不管資料列是否能配對，所有保留資料表(集)上的資料均將傳回，對於連結表格中無法配對的資料欄位則填入NULL值。在此將使用FULL OUTER JOIN即兩個資料表的聯集（最大集合），任何無法與另外資料表匹配的部分傳回NULL值，可參考《資料表連結(Table Join)概念》文章所述。
2. COALESCE()參數位置決定優先順序
前述步驟將產生兩者最大資料集，但無法配對的資料欄位則填入NULL值，資料至少存在於一方，可利用COALESCE函數特性，當第一個參數為NULL時，將以第二個參數取代（置換），因此只需將優先者至於第一個參數即可。COALESCE函數於此範例也可使用ISNULL（MSSQL）或NVL(ORACLE)，可參考《NULL處理應注意事項及常用函數》文章。
沒有留言:
張貼留言