SQL Server:APPLY表運算子

　　SQL Server 2005（含）以上版本，新增了APPLY表運算，為我們日常查詢帶來了極大的方便。

　　新增的APPLY表運算子把右表運算式應用到左表運算式中的每一行。它不像JOIN那樣先計算那個表運算式都可以，APPLY必選先邏輯地計算左運算式。這種計算輸入的邏輯順序允許吧右運算式關聯到左表運算式。

　　APPLY有兩種形式，一個是OUTER APPLY，一個是CROSS APPLY，區別在於指定OUTER，意味著結果集中將包含使右表運算式為空白的左表運算式中的行，而指定CROSS，則相反，結果集中不包含使右表運算式為空白的左表運算式中的行。

　　用幾個例子解釋這個會更清晰。

　　例1：CROSS APPLY 形式

　　比如：LargeTable表中的某一列儲存的資料是以“:”號分隔的資料，我們處理的時候，可能要先把這個值，先分隔，然後把分隔後的每個值單獨一行放在一張表中，然後對這個表做處理。

　　未經處理資料（LargeTable表）：
　　

　　為了簡單，我們先拿其中id=2的一行處理，這些以:號分隔的資料，可能是我們某張表的主鍵（t1），我們可能需要把這些數值提出來，放在一張暫存資料表中，和t1表關聯，做一些處理。

　　處理這個分隔的資料結果如：

　　

　　如果用之前的版本處理這個操作的話，應該很發雜，暫時沒想到怎麼處理，如果有人實現過，可以提示一下。

　　這隻是用其中一行做的處理，如果我們用的3行都做這樣處理，把三行以:號分隔的數值都放在一個表中，該怎麼處理呢？

　　今天的主角APPLY該閃亮登場了。用APPLY表運算子一行語句就能處理以上操作。

　　

SELECT a FROM dbo.LargeTable AS LT    --實際表
CROSS APPLY dbo.split(LT.Name,':')    --自訂表格值函數，處理以某個字元分隔的資料，把這些資料，返回一張表
WHERE a <> '' --去掉結果表中a欄位為空白的資料

　　處理的結果如：

　　

　　是不是很簡單。

     需要額外定義的就是那個自訂表格值函數（split），這是我在網上找的，類似.Net中Split操作，代碼如下：

　　

/*
    使用方法：SELECT * FROM dbo.split('581::579::519::279::406::361::560',':')
*/
ALTER Function [dbo].[Split](@Sql varchar(8000),@Splits varchar(10))
returns @temp Table (a varchar(100))
As
Begin
    Declare @i Int
    Set @Sql = RTrim(LTrim(@Sql))
    Set @i = CharIndex(@Splits,@Sql)
    While @i >= 1
    Begin
    Insert @temp Values(Left(@Sql,@i-1))
    Set @Sql = SubString(@Sql,@i+1,Len(@Sql)-@i)
    Set @i = CharIndex(@Splits,@Sql)
End

If @Sql <> ''
Insert @temp Values (@Sql)
Return
End

 

 　　例2：OUTER APPLY 形式

　　情境：有個供貨商表（Supplier）和供貨商產品表（Products）,我們要取每一個供貨商中單價最高的兩個產品。

　　供貨商表：

　　

　　供貨商產品表：

　　

　　首先，我們建立一個自訂表格值函數（dbo.fn_top_products），該函數根據供貨商ID返回單價最高的兩個商品。

IF OBJECT_ID('dbo.fn_top_products') IS NOT NULL
    DROP FUNCTION dbo.fn_top_products;
GO
--根據供貨商ID獲得單價最高的兩件商品
CREATE FUNCTION dbo.fn_top_products
    (@supid AS INT)
    RETURNS TABLE
AS
RETURN
    SELECT TOP(2)Id AS ProductId,ProductName,UnitPrice
    FROM dbo.Products
    WHERE SupplierId = @supid
    ORDER BY UnitPrice DESC
GO

      好，前期的資料都已經準備好了，下面讓我們試試用OUTER APPLY形式來查詢，會出現什麼結果。

      執行以下語句：

SELECT S.id AS SupplierId,S.CompanyName,UnitPrice FROM dbo.Supplier AS S
OUTER APPLY dbo.fn_top_products(S.id) AS P

　　執行結果如下：

　　　

　　注意最後為NULL的記錄，reed公司因為沒有商品，所以單價為NULL了。

　　如果用CROSS APPLY形式，執行以下查詢：

　　

SELECT S.id AS SupplierId,S.CompanyName,UnitPrice FROM dbo.Supplier AS S
CROSS APPLY dbo.fn_top_products(S.id) AS P　　　

　　產生的輸出結果如下：

　　　　

 

　　大家看出OUTER APPLY和CROSS APPLY的區別了吧。

 

　　再次說一下APPLY的執行過程，它先邏輯計算左表運算式（以上的LargeTable表），然後把右運算式（以上的自訂表格值函數Split）應用到左表運算式的每一行。實際是把外部查詢的列引用作為參數傳遞給資料表值函式。