程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> 更多數據庫知識 >> SQLServer APPLY表運算符使用介紹

SQLServer 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)應用到左表表達式的每一行。實際是把外部查詢的列引用作為參數傳遞給表值函數。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved