2013年9月6日 星期五

查詢股票最近之庫存數量,存在,則傳回最近之庫存數量,若無,仍需回傳0

當查詢特定股票最近之庫存數量時,若此檔股票尚有庫存數量,則傳回最近之庫存數量,若無,仍需回傳0,以供Stored Procedure或程式後續使用。本範例重點在於如何解決以下兩項問題:
1. 最近庫存資料值如何取得(重點1
取得最近庫存資料值,可使用ROW_NUMBER函數,根據庫存日期進行反向排序以產生序號,並擷取序號為1的資料列(Row)。

2. 無符合資料,仍需傳回0(重點2
利用純量彚總Scalar Aggregate)運算只傳回1筆資料列之特性,即SQL中無GROUP BY子句但使用彚總函數之語法,當無任何資料符合查詢條件時,仍將傳回1筆值為NULL之資料列;再配合ANSI SQLNULL置換函數-COALESCE即可解決必要傳回的問題,另外,ORACLE也可使用NVL函數,MSSQL則為ISNULL函數,不過仍建議應優先採用ANSI語法,可減少未來可能之移轉及教育訓練成本。

SQL
測試12330(有庫存)
測試22340(無庫存)
SELECT COALESCE(MAX(Stk_Qty), 0) Stk_Qty--重點(2)
FROM
(
SELECT S.*
, ROW_NUMBER() OVER(ORDER BY DT Desc) Seq --重點(1)
FROM Stocks S
WHERE 1=1
AND DT < '20130906'
AND Stk_No = '2330' --測試1: 存在
AND Stk_No = '2340' --測試2:
) A
WHERE 1=1
     AND Seq = 1 --重點(1)






使用彚總函數所產生1NULL資料,以COALESCE- NULL置換函數變更為0


可使用以下SQL產生資料
MSSQL
ORACEL
SELECT '20130905' DT
      , '2330' Stk_No
      , 120 Stk_Qty
     INTO Stocks
UNION ALL
SELECT '20130904', '2330', 100
UNION ALL
SELECT '20130903', '2330', 86
CREATE TABLE Stocks
AS
SELECT '20130905' DT
  , '2330' Stk_No
  , 120 Stk_Qty
FROM DUAL
UNION ALL
SELECT '20130904', '2330', 100
FROM DUAL
UNION ALL
SELECT '20130903', '2330', 86
FROM DUAL

沒有留言:

張貼留言