MSSQL2005起提供APPLY運算子，主要用於子查詢結果與資料表值函式(table-valued funtion)兩者間進行連結。運算時將每一筆查詢結果傳入資料表值函式中，將子查詢結果視為左側資料表，資料表值函式則當成右側資料表(集)再執行結合。APPLY運算子分成以下兩種方法
1. CROSS APPLY: 相當於使用INNER JOIN
傳回兩個資料表中相符(匹配)的記錄，即傳入資料表值函式可產生子查詢結果外其他資料表之資料列(Rows)。
2. OUTER APPLY: 相當於使用LEFT OUTER JOIN
子查詢為保留資料表（Preseved Table），將保有所有資料，資料表值函式可能產生或不會產生結果集的資料列（即未符合為NULL）。
前述資料表值函式(table-valued funtion)僅是MSSQL中常見用法，在此將應於資料反轉置(UNPIVOT)，如同於《資料反轉置(UNPIVOT)》文中案例如下所示。欲將Orders_Pivot資料表中"2009-08-01"、"2009-08-02"及"2009-08-03"等3個欄位（Column）中資料內容為應收帳款（AR）值，分別轉入新表格3筆資料列（Row）的AR欄位中，而原欄位名稱成為該筆資料的屬性值轉入Order_Date欄位中。
表格：訂單日期轉置資料表（四筆資料，三個轉置欄位）
註：2009-08-02張先生並任無應收帳款，當轉置後結果需將此筆資料剔除。
|
表格：應收帳款（A/R）彙總表（八筆資料）
|
反轉置(UNPIVOT)重要基本概念是將轉置欄位(Column)逐一轉換(擴增)為一筆資料列(Row)，APPLY運算子應用時會將資料逐筆傳入資料表值函式特性(在此使用MSSQL2008所提供VALUES增加功能:Table
Value Constructor)，同筆資料中3個欄位(Column)資料透過VALUES增加功能轉換成3筆資料列(Row)，資料內容將存於AR欄位中，而將資料欄位名稱(屬性)存於Order_Date中。
ORACLE 12C亦開始支援ANSI語法APPLY 運算子，使用方式與MSSQL雷同，資料是由子查詢結果中逐筆傳入，本案例需將3個欄位(Column)轉換成3筆資料列(Row)，而目前版本未支援MSSQL的VALUES增加功能，將透過UNION ALL組合查詢方式產生反轉置之3筆資料列(Row)，由於資料為子查詢逐筆傳入，本身無實際資料表，查詢語法上規定需搭配FROM子句，因此將使用虛擬資料表(DAUL)替代，SQL如下所示。
MSSQL
|
ORACLE
|
SELECT Cust_Name
, Order_Date
, AR
FROM Orders_Pivot
CROSS APPLY
(VALUES ("2009-08-01", '2009-08-01')
, ("2009-08-02", '2009-08-02')
, ("2009-08-03", '2009-08-03')
) X (AR, Order_Date)
WHERE 1=1
AND X.AR IS NOT NULL
ORDER BY Cust_Name
, Order_Date
|
SELECT CUST_NAME
, Order_Date
, AR
FROM Orders_Pivot
CROSS APPLY
(
SELECT "2009-08-01" AR --資料內容
, '2009-08-01' Order_Date--欄位名稱
FROM DUAL
UNION ALL
SELECT "2009-08-02" AR, '2009-08-02'
FROM DUAL
UNION ALL
SELECT "2009-08-03" AR, '2009-08-03'
FROM DUAL
)
X
WHERE 1=1
AND X.AR IS NOT NULL
ORDER BY CUST_NAME
, Order_Date
|
沒有留言:
張貼留言