SQL Server 關(guān)于CROSS APPLY 和 OUTER APPLY應(yīng)用

先看看語(yǔ)法：

再讓我們了解一下apply運(yùn)算涉及的兩個(gè)步驟：

A1：把右表表達(dá)式(

A2：添加外部行；

　　使用apply就像是先計(jì)算左輸入，然后為左輸入中的每一行計(jì)算一次右輸入。（這一句很重要，可能會(huì)不理解，但要先記住，后面會(huì)有詳細(xì)的說明）

最后結(jié)合以上兩個(gè)步驟說明cross apply和outer apply的區(qū)別：

　　crossapply和outer apply 總是包含步驟A1，只有outer apply包含步驟A2，如果crossapply左行應(yīng)用右表表達(dá)式時(shí)返回空積，則不返回該行。而outer apply返回該行，并且該行的右表表達(dá)式的屬性為null。

看到上面的解釋或步驟大家可能還是一頭的霧水，不知所云。

下面用例子來說明：

--表一（[dbo].[Customers]??字段說明：customerid--消費(fèi)者id，?city?--?所在城市）：
CREATE?TABLE?[dbo].[Customers](
????[customerid][char](5)NOTNULL,
????[city][varchar](10)NOTNULL,
PRIMARY?KEY?CLUSTERED?(?[customerid]ASC)WITH(IGNORE_DUP_KEY=OFF)ON?[PRIMARY]
)?ON?[PRIMARY]
--向表一插入數(shù)據(jù)：
insert?into?dbo.Customersvalues('FISSA','Madrid');
insert?into?dbo.Customersvalues('FRNDO','Madrid');
insert?into?dbo.Customersvalues('KRLOS','Madrid');
insert?into?dbo.Customersvalues('MRPHS','Zion');
--表二（[dbo].[Orders]??字段說明：orderid--訂單id?，customerid?--?消費(fèi)者id）：
CREATE?TABLE?[dbo].[Orders](
????[orderid]?[int]NOT?NULL,
????[customerid][char](5)NULL,
PRIMARY?KEY?CLUSTERED?([orderid]ASC)WITH(IGNORE_DUP_KEY=OFF)ON?[PRIMARY]
)?ON?[PRIMARY]
--向表二插入數(shù)據(jù)：
insert?into?dbo.Ordersvalues(1,'FRNDO');
insert?into?dbo.Ordersvalues(2,'FRNDO');
insert?into?dbo.Ordersvalues(3,'KRLOS');
insert?into?dbo.Ordersvalues(4,'KRLOS');
insert?into?dbo.Ordersvalues(5,'KRLOS');
insert?into?dbo.Ordersvalues(6,'MRPHS');
insert?into?dbo.Ordersvalues(7,null);
--查詢插入的數(shù)據(jù)：
select?*from?dbo.Customers
select?*from?dbo.orders

結(jié)果：
customerid?city
----------?----------
FISSA??????Madrid
FRNDO??????Madrid
KRLOS??????Madrid
MRPHS??????Zion
?
(4?行受影響)
?
orderid?????customerid
-----------?----------
1???????????FRNDO
2???????????FRNDO
3???????????KRLOS
4???????????KRLOS
5???????????KRLOS
6???????????MRPHS
7???????????NULL
?
(7?行受影響)

?

【例子】得到每個(gè)消費(fèi)者最新的兩個(gè)訂單：

1、用cross apply

SELECT??*
FROM????dbo.CustomersAS?A
????????CROSS?APPLY(?SELECT?TOP?2?*
?????????????????????FROM??????dbo.OrdersAS?B
?????????????????????WHERE?????A.customerid=?B.customerid
?????????????????????ORDER?BY??orderid?DESC
????????????????????)?AS?AB;

結(jié)果：

customerid?city???????orderid?????customerid
----------?----------?---------------------
FRNDO??????Madrid????2???????????FRNDO
FRNDO??????Madrid????1???????????FRNDO
KRLOS??????Madrid????5???????????KRLOS
KRLOS??????Madrid????4???????????KRLOS
MRPHS?????Zion???????6???????????MRPHS

過程分析：

　　它是先得出左表【dbo.Customers】里的數(shù)據(jù)，然后把此數(shù)據(jù)一條一條的放入右表表式中，分別得出結(jié)果集，最后把結(jié)果集整合到一起就是最終的返回結(jié)果集了（T1的數(shù)據(jù) 像for循環(huán)一樣 一條一條的進(jìn)入到T2中 然后返回一個(gè)集合?最后把所有的集合整合到一塊?就是最終的結(jié)果），最后我們?cè)倮斫庖幌律厦孀層浿脑挘ㄊ褂胊pply就像是先計(jì)算左輸入，讓后為左輸入中的每一行計(jì)算一次右輸入）。

2、用outer apply

SELECT??*
FROM????dbo.CustomersAS?A
????????OUTER?APPLY(?SELECT?TOP?2
????????????????????????????????*
?????????????????????FROM??????dbo.OrdersAS?B
?????????????????????WHERE?????A.customerid=?B.customerid
?????????????????????ORDER?BY??orderid?DESC
????????????????????)?AS?AB;

結(jié)果：

customerid?city???????orderid?????customerid
----------?----------?---------------------
FISSA??????Madrid????NULL????????NULL
FRNDO??????Madrid????2???????????FRNDO
FRNDO??????Madrid????1???????????FRNDO
KRLOS??????Madrid????5???????????KRLOS
KRLOS??????Madrid????4???????????KRLOS
MRPHS?????Zion???????6???????????MRPHS

結(jié)果分析：

　　發(fā)現(xiàn)outerapply得到的結(jié)果比cross多了一行，我們結(jié)合上面所寫的區(qū)別（cross apply和outer apply 總是包含步驟A1，只有outer apply包含步驟A2，如果cross apply左行應(yīng)用右表表達(dá)式時(shí)返回空積，則不返回該行。而outerapply返回改行，并且改行的右表表達(dá)式的屬性為null）就會(huì)知道了。

【例二】

;with?tb1(客戶號(hào),銷售日期,銷售額)AS(
????select?'001','2017-05-01',460?union?all
????select?'001','2017-05-02',240?union??all
????select?'001','2017-05-03',300?
),TB2(客戶號(hào),付款額)AS(
????select??'001',500
)
SELECT??t1.客戶號(hào)?,
????????t1.銷售日期?,
????????t1.銷售額?,
????????CASE?WHEN?t2.付款額?=?t2.付款額
???????????????????????THEN?t2.付款額?-?ISNULL(p_銷售額,?0)
???????????????????????ELSE?t1.銷售額
??????????????????END
????????END?AS?實(shí)付額
FROM????tb1?AS?t1
????????CROSS?APPLY?(?SELECT????SUM(付款額)?AS?付款額
??????????????????????FROM??????TB2
??????????????????????WHERE?????TB2.客戶號(hào)?=?t1.客戶號(hào)
????????????????????)?AS?t2
????????OUTER?APPLY?(?SELECT????SUM(銷售額)?AS?p_銷售額
??????????????????????FROM??????tb1?AS?tt
??????????????????????WHERE?????tt.客戶號(hào)?=?t1.客戶號(hào)
????????????????????????????????AND?tt.銷售日期?<?t1.銷售日期
????????????????????)?AS?tt1;

--結(jié)果
客戶號(hào)??銷售日期???????銷售額?????????實(shí)付額
----?----------?-----------?-----------
001??2017-05-01?460?????????460
001??2017-05-02?240?????????40
001??2017-05-03?300?????????0