前言

其實(shí)有些新的特性在SQL Server早就已經(jīng)出現(xiàn)過，但是若非系統(tǒng)的去學(xué)習(xí)數(shù)據(jù)庫你會(huì)發(fā)現(xiàn)在實(shí)際項(xiàng)目中別人的SQL其實(shí)是比較復(fù)雜的，其實(shí)利用新的SQL Server語法會(huì)更加方便和簡(jiǎn)潔，從本節(jié)開始我們將講述一些SQL Server中早已出現(xiàn)的新語法，簡(jiǎn)短的內(nèi)容，深入的理解，Always to reivew the basics。

初探APPLY運(yùn)算符

APPLY運(yùn)算符是一個(gè)非常強(qiáng)大的表運(yùn)算符，但是APPLY不是標(biāo)準(zhǔn)的，相對(duì)應(yīng)的標(biāo)準(zhǔn)叫做LATERAL，但是此標(biāo)準(zhǔn)并未在SQL Server中實(shí)現(xiàn)。像所有表運(yùn)算符一樣，該運(yùn)算符用于查詢的FROM子句中。APPLY運(yùn)算符支持的類型是CROSS APPLY和OUTER APPLY。CROSS APPY僅僅實(shí)施一個(gè)邏輯查詢處理階段，而OUTER APPLY實(shí)施了兩個(gè)階段，APPLY運(yùn)算符對(duì)兩個(gè)輸入表進(jìn)行操作，第二個(gè)可以是一個(gè)表表達(dá)式，我們將APPLY兩側(cè)的表分別叫做左側(cè)表和右側(cè)表，右側(cè)表通常是一個(gè)派生表或TVF（內(nèi)嵌表值函數(shù)）。CROSS APPLY運(yùn)算符實(shí)施一個(gè)邏輯查詢處理階段-它將右側(cè)的表表達(dá)式應(yīng)用到左側(cè)表的每一行，并生成一個(gè)組合結(jié)果集的結(jié)果表。CROSS APPLYl類似于交叉聯(lián)接中的CROSS JOIN，但是使用CROSS APPLY運(yùn)算符，右側(cè)的表表達(dá)式可以對(duì)來自左側(cè)表的每一行表示一個(gè)不同的行集，這是與聯(lián)接的不同之處。當(dāng)在右側(cè)使用一個(gè)派生表，并且派生表查詢中引用來自左側(cè)表的屬性，就可以實(shí)現(xiàn)此目標(biāo)，或者是在右側(cè)使用一個(gè)內(nèi)嵌TVF，可以傳遞左側(cè)的屬性作為輸入?yún)?shù)，同樣可以實(shí)現(xiàn)此目的-摘抄自SQL Server 2012基礎(chǔ)教程。下面我們看一個(gè)簡(jiǎn)單的例子。

USE TSQL2012 GOSELECT C.custid, A.orderid, A.orderdate FROM Sales.Customers AS CCROSS APPLY(SELECT TOP(3) orderid, empid, orderdate, requireddate FROM Sales.Orders AS OWHERE O.custid = C.custidORDER BY orderdate DESC, orderid DESC) AS A;

上述完成的是返回每個(gè)客戶最近的3個(gè)訂單。我們可以將右側(cè)的表表達(dá)式看做是一個(gè)相關(guān)子查詢，右側(cè)的表表達(dá)式通過引用custid對(duì)來自Customers表的每一行進(jìn)行處理并返回每個(gè)客戶的最近的3個(gè)訂單，是不是看起來很清爽呢，下面我們將進(jìn)一步探討APPLY運(yùn)算符的作用。

進(jìn)一步探討APPLY運(yùn)算符

上面我們看到通過相關(guān)子查詢來進(jìn)行查詢顯得代碼有點(diǎn)丑陋，我們?cè)賮砜匆粋€(gè)例子。查詢每個(gè)單價(jià)最高的訂單，我們通過子查詢來實(shí)現(xiàn)。

CROSS APPLY

USE AdventureWorks2012 GOSELECT SalesOrderID,OrderDate,MaxUnitPrice =(SELECT MAX(sod.UnitPrice) FROM Sales.SalesOrderDetail sod WHERE soh.SalesOrderID = sod.SalesOrderID) FROM Sales.SalesOrderHeader AS soh

如上操作看似代碼比較簡(jiǎn)潔也能完成我們的查詢?cè)V求，但是我們用派生表來進(jìn)行查詢又是怎樣的呢？?

USE AdventureWorks2012 GOSELECT soh.SalesOrderID,soh.OrderDate,sod.max_unit_price FROM Sales.SalesOrderHeader AS soh JOIN (SELECT max_unit_price = MAX(sod.UnitPrice),SalesOrderIDFROM Sales.SalesOrderDetail AS sodGROUP BY sod.SalesOrderID ) sod ON sod.SalesOrderID = soh.SalesOrderID

此時(shí)由于兩個(gè)表完全不相關(guān)，我們需要通過GROUP BY完成再進(jìn)行JOIN，代碼不是顯得非常臃腫嗎，這還是簡(jiǎn)單的，當(dāng)有多個(gè)表時(shí)就比較復(fù)雜了，導(dǎo)致代碼就不再具有可讀性。但是自從在SQL Server 2005中有了APPLY媽媽再也不用擔(dān)心我讀不懂復(fù)雜的代碼了，我們看看CROSS APPLY是怎樣實(shí)現(xiàn)的。

USE AdventureWorks2012 GOSELECT soh.SalesOrderID,soh.OrderDate,sod.max_unit_price FROM Sales.SalesOrderHeader AS soh CROSS APPLY (SELECT max_unit_price = MAX(sod.UnitPrice)FROM Sales.SalesOrderDetail AS sodWHERE soh.SalesOrderID = sod.SalesOrderID ) sod

當(dāng)我們利用內(nèi)部聯(lián)接時(shí)此時(shí)JOIN中的查詢是獨(dú)立的所以需要進(jìn)行GROUP BY，而對(duì)于CROSS APPLY它本身就是對(duì)來自左側(cè)的表中每一行就行處理并返回，同時(shí)利用CROSS APPLY它也超越了相關(guān)子查詢，比如說我們還需要查出每個(gè)訂單的總價(jià)呢，我們利用相關(guān)子查詢需要再次嵌入SELECT子句。

SELECT SalesOrderID ,OrderDate ,MaxUnitPrice = (SELECT MAX(sod.UnitPrice) FROM Sales.SalesOrderDetail sod WHERE soh.SalesOrderID = sod.SalesOrderID),SumLineTotal = (SELECT SUM(LineTotal) FROM Sales.SalesOrderDetail sod WHERE soh.SalesOrderID = sod.SalesOrderID) FROM Sales.SalesOrderHeader AS soh

而利用CROSS APPLY只需添加集合函數(shù)SUM即可

USE AdventureWorks2012 GOSELECT soh.SalesOrderID,soh.OrderDate,sod.max_unit_price,sod.sum_line_total FROM Sales.SalesOrderHeader AS soh CROSS APPLY (SELECT max_unit_price = MAX(sod.UnitPrice),sum_line_total = SUM(sod.LineTotal)FROM Sales.SalesOrderDetail AS sodWHERE soh.SalesOrderID = sod.SalesOrderID ) sod?

OUTER APPLY

對(duì)于OUTER APPLY，如果右側(cè)的表表達(dá)式返回一個(gè)空集合，CROSS APPLY運(yùn)算符不會(huì)返回相應(yīng)的左側(cè)行，也就是說OUTER APPLY和在派生表上進(jìn)行LEFT JOIN是等同的，如下：

SELECT soh.SalesOrderID,soh.OrderDate,sod.max_unit_price FROM Sales.SalesOrderHeader AS soh LEFT JOIN (SELECT max_unit_price = MAX(sod.UnitPrice),SalesOrderIDFROM Sales.SalesOrderDetail AS sodGROUP BY sod.SalesOrderID ) sod ON sod.SalesOrderID = soh.SalesOrderID

此時(shí)我們利用OUTER APPLY則是如下：

USE AdventureWorks2012 GOSELECT soh.SalesOrderID,soh.OrderDate,sod.max_unit_price FROM Sales.SalesOrderHeader AS soh OUTER APPLY (SELECT max_unit_price = MAX(sod.UnitPrice)FROM Sales.SalesOrderDetail AS sodWHERE soh.SalesOrderID = sod.SalesOrderID ) sod

上述對(duì)于APPLY右側(cè)表表達(dá)式是一個(gè)派生表，此時(shí)為了封裝，我們可以使用TVF內(nèi)嵌表值函數(shù)來實(shí)現(xiàn)。其實(shí)將內(nèi)嵌表值函數(shù)來代替派生表實(shí)現(xiàn)每個(gè)客戶最近的3個(gè)訂單。首先我們封裝一個(gè)表值函數(shù)

USE TSQL2012 GOIF OBJECT_ID('dbo.TopOrders') IS NOT NULLDROP FUNCTION dbo.TopOrders; GOCREATE FUNCTION dbo.TopOrders(@custid AS INT, @n AS INT)RETURNS TABLE AS RETURNSELECT orderid, empid, orderdate, requireddateFROM Sales.OrdersWHERE custid = @custidORDER BY orderdate DESC, orderid DESCOFFSET 0 ROWS FETCH FIRST @n ROWS ONLY; GO

接著利用CROSS APPLY進(jìn)行查詢。

USE TSQL2012 GOSELECT C.custid, C.companyname, A.orderid, A.empid, A.requireddate FROM Sales.Customers AS CCROSS APPLY dbo.TopOrders(C.custid, 3) AS A;

上面我們通過封裝內(nèi)嵌表值函數(shù)代替派生表使代碼更具可讀性和可維護(hù)性。到此我們可以得出一點(diǎn)基本結(jié)論。

APPLY運(yùn)算符使用分析結(jié)論：當(dāng)需要對(duì)表中的每一行進(jìn)行應(yīng)用時(shí)，且需要將所有結(jié)果集組合到一個(gè)結(jié)果集表中時(shí)，此時(shí)我們應(yīng)該使用APPLY運(yùn)算符，至于是使用CROSS APPLY還是OUTER APPLY根據(jù)場(chǎng)景而定，雖然APPLY右側(cè)表可以用相關(guān)子查詢或者派生表來實(shí)現(xiàn)，但是使得代碼臃腫和可維護(hù)性差，通過封裝內(nèi)嵌表值函數(shù)來實(shí)現(xiàn)可以說是對(duì)右側(cè)表通過相關(guān)子查詢或者派生表來實(shí)現(xiàn)的完美替代者。

總結(jié)

本節(jié)我們講解了APPLY運(yùn)算符中兩種類型的使用，下一節(jié)我們來分析下關(guān)于CROSS APPLY VS INNER JOIN的性能問題，同時(shí)也說明下CROSS APPLY和OUTER APPLY的應(yīng)用場(chǎng)景。簡(jiǎn)短的內(nèi)容，深入的理解，我們下節(jié)再會(huì)。

轉(zhuǎn)載于:https://www.cnblogs.com/CreateMyself/p/6193183.html

總結(jié)

以上是生活随笔為你收集整理的SQL Server-聚焦APPLY运算符（二十七）的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。