CROSS APPLY 與 OUTER APPLY查詢

jeffluo

問題:將文字資料提取關鍵詞並整理成組合表(例如 A_B_C 為1；C_D_E 為2 ；A_F為3) 組合內容不一，可能單一也可能重複。

使用case_when和function將已知的組合重組資料，但資料無法彈性運用，且一旦增加資料就要重新確認組合不易維護

請教先進們應該用何種方法進行較佳呢？

解答:

use tempdb

go

--產稱對應表

if object_id('tblMap') is not null

drop table tblMap

go

--產生實體資料表

if object_id('tblData') is not null

drop table tblData

go

--產生對應TVF(TABLE VALUED FUNCTION)

if object_id('fn_Map') is not null

drop function fn_Map

go

--對應表

create table tblMap

(src varchar(12),

result int)

go

--新增對應資料

insert into tblMap(src,result)

values('A_B_C',1),('C_D_E',2),('A_F',3)

go

--實體資料表

create table tblData

(xid int identity,

xdata varchar(10))

go

--新增實體資料

insert into tblData(xdata)

values('A_B_C'),('A_B_C'),('C_D_E'),('C_D_E'),('A_F')

,('A_F'),('A_F'),('A_Q')

go

--建立TVF

create function dbo.fn_Map

(@src varchar(10))

returns table

as

--根據輸入值回傳對應代碼

return(

    select result

    from tblMap

where src=@src)

go

--驗證資料

select * from tblData

select * from tblMap

go

--完成查詢

select a.*,b.*

from tblData a outer apply dbo.fn_Map(xdata) as b

go

--檢視對應結果

CROSS APPLY 與 OUTER APPLY查詢

新版本的SQL Server 2005~2016所新增的APPLY運算子，可以在SELECT的查詢陳述之中，搭配資料表值使用者自訂函數，回傳資料列進行合併查詢。當學習這個運算子的使用之前，需要稍微瞭解資料表值使用者自訂函數的建立與使用方式，本章僅會進行簡易的建立與使用方式，深入的說明可以參閱『第九章的使用者自訂函數』的說明。

以下的範例會先建立一個資料表儲存所有的測試資料，該資料表主要是儲存產品代號與產品名稱。此外還會建立一個資料表值的使用者自訂函數。

--建立儲存產品代號與名稱的資料表 USE AdventureWorks GO CREATE TABLE t_parts (partNumber INT,     --料件號碼 partdesc VARCHAR(6) --產品說明 ) GO

建立完成資料表之後，接著新增三筆測試資料。過程中使用INSERT的陳述式，該INSERT的陳述式將會在後續章節中進行說明。

--新增三筆產品基本資料 INSERT INTO t_parts VALUES (99, '汽車') INSERT INTO t_parts VALUES (333,'電腦') INSERT INTO t_parts VALUES (444,'休閒') GO

此外建立出貨記錄資料表，包含數量與產品代號，這個資料表主要是模擬每一項產品的出貨記錄。

--建立出貨資料表與新增測試資料 USE AdventureWorks GO CREATE TABLE t_orderLineItem ( [order] INT,   --訂單序號 partNumber INT --料件號碼 ) GO --新增三筆出貨記錄 --注意第99號產品沒有出貨記錄。 INSERT INTO t_orderLineItem VALUES (100, 333) INSERT INTO t_orderLineItem VALUES (200, 333) INSERT INTO t_orderLineItem VALUES (200, 444) GO

接著建立一個資料表值的使用者自訂函數，它的主要定義就是輸入產品代號，回傳所有出貨記錄數量與產品代號的資料集。建立指令與說明如下。

--建立資料表值使用者自訂函數 USE AdventureWorks GO CREATE FUNCTION udf_partsOnOrders ( @partNumber INT ) --輸入產品代號 RETURNS TABLE        --回傳所有的出貨記錄 AS RETURN (         SELECT [order] as Quantity     --改變輸出名稱為Quantity         FROM  t_orderLineItem         WHERE  partNumber=@partNumber  --找出符合的產品         ) GO

測試的過程可以直接使用SELECT的查詢陳述式，搭配該函數在FROM子句中使用，並且輸入料號參數就可以回傳該料號對應的出貨記錄。

--測試使用者自訂函數 USE AdventureWorks --在FROM子句中使用該資料表值函數，並且輸入333產品參數 SELECT * FROM dbo.udf_partsOnOrders(333) GO --結果 Quantity ----------- 100 200

如果要找出所有產品與出貨記錄時，可以在CROSS APPLY運算子左邊置放產品資料表(t_parts)，右邊置放資料表值使用者自訂函數(dbo.udf_partsOnOrders)並且使用產品代號作為參數傳遞。以下就是範例程式。

--使用CROSS APPLY搭配資料表值使用者自訂函數 SELECT * FROM dbo.t_parts CROSS APPLY     dbo.udf_partsOnOrders(partNumber) GO --結果 partNumber  partdesc Quantity ----------- -------- ----------- 333         電腦       100 333         電腦       200 444         休閒       200 (3 個資料列受到影響)

上述的陳述式主要的核心技術就是，當查詢dbo.t_parts資料表的時候，藉由CROSS APPLY運算子，將dbo.t_parts資料表的partNumber資料行，傳給dbo.udf_partsOnOrders函數，當成輸入參數。

深入瞭解上述的輸出結果，可以發現99號的產品雖然沒有出貨記錄，但是整個輸出結果卻自動刪除該筆資料的顯示，所以當使用APPLY運算子，除了一般的CROSS使用技巧之外，也可以搭配OUTER針對所有資料列的查詢。以下就是找出三種產品中，無出貨記錄者。

--使用OUTER APPLY搭配資料表值使用者自訂函數 SELECT * FROM t_parts OUTER APPLY      dbo.udf_partsOnOrders(partNumber) GO --結果，就會出現所有產品料號 partNumber  partdesc Quantity ----------- -------- ----------- 99          汽車       NULL 333         電腦       100 333         電腦       200 444         休閒       200 (4 個資料列受到影響)