SQL Server 關(guān)于CROSS APPLY 和 OUTER APPLY應(yīng)用
[導(dǎo)讀]先看看語法：再讓我們了解一下apply運算涉及的兩個步驟：A1：把右表表達式(A2：添加外部行； 使用apply就像是先計算左輸入，然后為左輸入中的每一行計算一次右輸入。（這一句很重要，可能會不理解
先看看語法：
再讓我們了解一下apply運算涉及的兩個步驟：
A1：把右表表達式(
A2：添加外部行；
使用apply就像是先計算左輸入，然后為左輸入中的每一行計算一次右輸入。（這一句很重要，可能會不理解，但要先記住，后面會有詳細的說明）
最后結(jié)合以上兩個步驟說明cross apply和outer apply的區(qū)別：
crossapply和outer apply 總是包含步驟A1，只有outer apply包含步驟A2，如果crossapply左行應(yīng)用右表表達式時返回空積，則不返回該行。而outer apply返回該行，并且該行的右表表達式的屬性為null。
看到上面的解釋或步驟大家可能還是一頭的霧水，不知所云。
下面用例子來說明：
--表一（[dbo].[Customers]??字段說明：customerid--消費者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?--?消費者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?行受影響)
?
【例子】得到每個消費者最新的兩個訂單：
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)一樣 一條一條的進入到T2中 然后返回一個集合?最后把所有的集合整合到一塊?就是最終的結(jié)果），最后我們再理解一下上面讓記著的話（使用apply就像是先計算左輸入，讓后為左輸入中的每一行計算一次右輸入）。
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)用右表表達式時返回空積，則不返回該行。而outerapply返回改行，并且改行的右表表達式的屬性為null）就會知道了。
【例二】
;with?tb1(客戶號,銷售日期,銷售額)AS( ????select?'001','2017-05-01',460?union?all ????select?'001','2017-05-02',240?union??all ????select?'001','2017-05-03',300? ),TB2(客戶號,付款額)AS( ????select??'001',500 ) SELECT??t1.客戶號?, ????????t1.銷售日期?, ????????t1.銷售額?, ????????CASE?WHEN?t2.付款額?=?t2.付款額 ???????????????????????THEN?t2.付款額?-?ISNULL(p_銷售額,?0) ???????????????????????ELSE?t1.銷售額 ??????????????????END ????????END?AS?實付額 FROM????tb1?AS?t1 ????????CROSS?APPLY?(?SELECT????SUM(付款額)?AS?付款額 ??????????????????????FROM??????TB2 ??????????????????????WHERE?????TB2.客戶號?=?t1.客戶號 ????????????????????)?AS?t2 ????????OUTER?APPLY?(?SELECT????SUM(銷售額)?AS?p_銷售額 ??????????????????????FROM??????tb1?AS?tt ??????????????????????WHERE?????tt.客戶號?=?t1.客戶號 ????????????????????????????????AND?tt.銷售日期?<?t1.銷售日期 ????????????????????)?AS?tt1; --結(jié)果 客戶號??銷售日期???????銷售額?????????實付額 ----?----------?-----------?----------- 001??2017-05-01?460?????????460 001??2017-05-02?240?????????40 001??2017-05-03?300?????????0
下載文檔
