IT技術互動交流平台

彩票大赢家官网

作者︰JeffckyWang  來(lai)源︰IT165收集(ji)  發布日期︰2020-02-26 14:41:17

彩票大赢家官网

其(qi)實(shi)有些新(xin)的特(te)性在SQL Server早(zao)就(jiu)已(yi)經出現過,但(dan)是若非系(xi)統的去學(xue)習數據庫你會發現在實(shi)際項目(mu)中別人的SQL其(qi)實(shi)是比較復雜的,其(qi)實(shi)利用新(xin)的SQL Server語法會更加方便(bian)和簡(jian)潔,從本節開(kai)始我們將(jiang)講述一(yi)些SQL Server中早(zao)已(yi)出現的新(xin)語法,簡(jian)短的內(na)容,深(shen)入的理(li)解,Always to reivew the basics。

初探APPLY運算符(fu)

APPLY運算符(fu)是一(yi)個非常強大的表運算符(fu),但(dan)是APPLY不是標準的,相對應的標準叫做LATERAL,但(dan)是此標準並(bing)未(wei)在SQL Server中實(shi)現。像所有表運算符(fu)一(yi)樣,該運算符(fu)用于查詢(xun)的FROM子句中。APPLY運算符(fu)支持的類型是CROSS APPLY和OUTER APPLY。CROSS APPY僅僅實(shi)施(shi)一(yi)個邏輯查詢(xun)處理(li)階段(duan),而(er)OUTER APPLY實(shi)施(shi)了兩個階段(duan),APPLY運算符(fu)對兩個輸入表進行操(cao)作,第二個可以是一(yi)個表表達式,我們將(jiang)APPLY兩側的表分(fen)別叫做左側表和右側表,右側表通常是一(yi)個派(pai)生表或TVF(內(na)嵌表值函數)。CROSS APPLY運算符(fu)實(shi)施(shi)一(yi)個邏輯查詢(xun)處理(li)階段(duan)-它將(jiang)右側的表表達式應用到左側表的每一(yi)行,並(bing)生成一(yi)個組合結果集(ji)的結果表。CROSS APPLYl類似(si)于交叉聯接中的CROSS JOIN,但(dan)是使(shi)用CROSS APPLY運算符(fu),右側的表表達式可以對來(lai)自左側表的每一(yi)行表示一(yi)個不同的行集(ji),這是與聯接的不同之處。當(dang)在右側使(shi)用一(yi)個派(pai)生表,並(bing)且派(pai)生表查詢(xun)中引(yin)用來(lai)自左側表的屬(shu)性,就(jiu)可以實(shi)現此目(mu)標,或者是在右側使(shi)用一(yi)個內(na)嵌TVF,可以傳遞左側的屬(shu)性作為輸入參(can)數,同樣可以實(shi)現此目(mu)的-摘抄自SQL Server 2012基礎教程。下(xia)面我們看一(yi)個簡(jian)單的例子。

USE TSQL2012GOSELECT C.custid, A.orderid, A.orderdateFROM Sales.Customers AS C CROSS APPLY (SELECT TOP(3) orderid, empid, orderdate, requireddate  FROM Sales.Orders AS O WHERE O.custid = C.custid ORDER BY orderdate DESC, orderid DESC) AS A;

上述完(wan)成的是返回每個客戶最(zui)近的3個訂(ding)單。我們可以將(jiang)右側的表表達式看做是一(yi)個相關子查詢(xun),右側的表表達式通過引(yin)用custid對來(lai)自Customers表的每一(yi)行進行處理(li)並(bing)返回每個客戶的最(zui)近的3個訂(ding)單,是不是看起來(lai)很清(qing)爽呢,下(xia)面我們將(jiang)進一(yi)步探討APPLY運算符(fu)的作用。

進一(yi)步探討APPLY運算符(fu)

上面我們看到通過相關子查詢(xun)來(lai)進行查詢(xun)顯得(de)代(dai)碼有點(dian)丑陋,我們再來(lai)看一(yi)個例子。查詢(xun)每個單價(jia)最(zui)高(gao)的訂(ding)單,我們通過子查詢(xun)來(lai)實(shi)現。

彩票大赢家官网

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

如上操(cao)作看似(si)代(dai)碼比較簡(jian)潔也能完(wan)成我們的查詢(xun)訴求,但(dan)是我們用派(pai)生表來(lai)進行查詢(xun)又是怎(zen)樣的呢? 

USE AdventureWorks2012GOSELECT soh.SalesOrderID ,soh.OrderDate ,sod.max_unit_priceFROM Sales.SalesOrderHeader AS sohJOIN( SELECT  max_unit_price = MAX(sod.UnitPrice), SalesOrderID FROM Sales.SalesOrderDetail AS sod GROUP BY sod.SalesOrderID) sodON sod.SalesOrderID = soh.SalesOrderID

此時由于兩個表完(wan)全不相關,我們需要通過GROUP BY完(wan)成再進行JOIN,代(dai)碼不是顯得(de)非常臃fen)茁ma),這還是簡(jian)單的,當(dang)有多(duo)個表時就(jiu)比較復雜了,導致代(dai)碼就(jiu)不再具有可讀性。但(dan)是自從在SQL Server 2005中有了APPLY媽(ma)媽(ma)再也不用擔(dan)心我讀不懂復雜的代(dai)碼了,我們看看CROSS APPLY是怎(zen)樣實(shi)現的。

USE AdventureWorks2012GOSELECT soh.SalesOrderID ,soh.OrderDate ,sod.max_unit_priceFROM Sales.SalesOrderHeader AS sohCROSS APPLY( SELECT  max_unit_price = MAX(sod.UnitPrice) FROM Sales.SalesOrderDetail AS sod WHERE soh.SalesOrderID = sod.SalesOrderID) sod

當(dang)我們利用內(na)部聯接時此時JOIN中的查詢(xun)是獨立的所以需要進行GROUP BY,而(er)對于CROSS APPLY它本身就(jiu)是對來(lai)自左側的表中每一(yi)行就(jiu)行處理(li)並(bing)返回,同時利用CROSS APPLY它也超越了相關子查詢(xun),比如說我們還需要查出每個訂(ding)單的總價(jia)呢,我們利用相關子查詢(xun)需要再次嵌入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

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

USE AdventureWorks2012GOSELECT soh.SalesOrderID ,soh.OrderDate ,sod.max_unit_price ,sod.sum_line_totalFROM Sales.SalesOrderHeader AS sohCROSS APPLY( SELECT  max_unit_price = MAX(sod.UnitPrice) ,sum_line_total = SUM(sod.LineTotal) FROM Sales.SalesOrderDetail AS sod WHERE soh.SalesOrderID = sod.SalesOrderID) sod 

彩票大赢家官网

對于OUTER APPLY,如果右側的表表達式返回一(yi)個空(kong)集(ji)合,CROSS APPLY運算符(fu)不會返回相應的左側行,也就(jiu)是說OUTER APPLY和在派(pai)生表上進行LEFT JOIN是等(deng)同的,如下(xia)︰

SELECT soh.SalesOrderID ,soh.OrderDate ,sod.max_unit_priceFROM Sales.SalesOrderHeader AS sohLEFT JOIN( SELECT  max_unit_price = MAX(sod.UnitPrice), SalesOrderID FROM Sales.SalesOrderDetail AS sod GROUP BY sod.SalesOrderID) sodON sod.SalesOrderID = soh.SalesOrderID

此時我們利用OUTER APPLY則是如下(xia)︰

USE AdventureWorks2012GOSELECT soh.SalesOrderID ,soh.OrderDate ,sod.max_unit_priceFROM Sales.SalesOrderHeader AS sohOUTER APPLY( SELECT  max_unit_price = MAX(sod.UnitPrice) FROM Sales.SalesOrderDetail AS sod WHERE soh.SalesOrderID = sod.SalesOrderID) sod

上述對于APPLY右側表表達式是一(yi)個派(pai)生表,此時為了封裝,我們可以使(shi)用TVF內(na)嵌表值函數來(lai)實(shi)現。其(qi)實(shi)將(jiang)內(na)嵌表值函數來(lai)代(dai)替派(pai)生表實(shi)現每個客戶最(zui)近的3個訂(ding)單。首(shou)先(xian)我們封裝一(yi)個表值函數

USE TSQL2012GOIF OBJECT_ID('dbo.TopOrders') IS NOT NULL DROP FUNCTION dbo.TopOrders;GOCREATE FUNCTION dbo.TopOrders (@custid AS INT, @n AS INT) RETURNS TABLEAS RETURN SELECT orderid, empid, orderdate, requireddate FROM Sales.Orders WHERE custid = @custid ORDER BY orderdate DESC, orderid DESC OFFSET 0 ROWS FETCH FIRST @n ROWS ONLY;GO

接著利用CROSS APPLY進行查詢(xun)。

USE TSQL2012GOSELECT C.custid, C.companyname, A.orderid, A.empid, A.requireddateFROM Sales.Customers AS C CROSS APPLY dbo.TopOrders(C.custid, 3) AS A;

上面我們通過封裝內(na)嵌表值函數代(dai)替派(pai)生表使(shi)代(dai)碼更具可讀性和可維護(hu)性。到此我們可以得(de)出一(yi)點(dian)基本結論。

APPLY運算符(fu)使(shi)用分(fen)析結論︰當(dang)需要對表中的每一(yi)行進行應用時,且需要將(jiang)所有結果集(ji)組合xi)揭yi)個結果集(ji)表中時,此時我們應該使(shi)用APPLY運算符(fu),至于是使(shi)用CROSS APPLY還是OUTER APPLY根(gen)據場景(jing)而(er)定(ding),雖然APPLY右側表可以用相關子查詢(xun)或者派(pai)生表來(lai)實(shi)現,但(dan)是使(shi)得(de)代(dai)碼臃fen)綴涂晌 hu)性差,通過封裝內(na)嵌表值函數來(lai)實(shi)現可以說是對右側表通過相關子查詢(xun)或者派(pai)生表來(lai)實(shi)現的完(wan)美替代(dai)者。

總結

本節我們講解了APPLY運算符(fu)中兩種類型的使(shi)用,下(xia)一(yi)節我們來(lai)分(fen)析下(xia)關于CROSS APPLY VS INNER JOIN的性能問題,同時也說de)饗xia)CROSS APPLY和OUTER APPLY的應用場景(jing)。簡(jian)短的內(na)容,深(shen)入的理(li)解,我們下(xia)節再會。

Tag標簽︰運算符(fu)  
  • 彩票大赢家官网

About IT165 - 彩票代理官网廣(guang)告服務 - 隱私聲明 - 版權申(shen)明 - 免責條(tiao)款 - 網站地(di)圖 - 網友(you)投(tou)稿 - 聯系(xi)方式
本站內(na)容來(lai)自于互聯網,僅供(gong)用于網絡技術學(xue)習,學(xue)習中請遵循相關法律法規
彩票大赢家官网 | 下一页