相關博客： SQL中ROW_NUMBER和APPLY在處理TOP N等類似問題的一點比較 SQL Server-聚焦APPLY運算符（二十七） 你真的會玩SQL嗎？冷落的Top和Apply 有以下應用場景 當用到了row_number over做分組排序時，可以考慮用apply...top替換 ro ...
相關博客：
SQL中ROW_NUMBER和APPLY在處理TOP N等類似問題的一點比較
有以下應用場景
- 當用到了row_number over做分組排序時，可以考慮用apply...top替換
row_number over語句：
SELECT A.*
FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY O.employeeID ORDER BY O.orderdate DESC) AS ROW,
E.LastName,
E.FirstName,
O.*
FROM Employees E
JOIN Orders O
ON E.EmployeeID = O.EmployeeID) A
WHERE A.ROW <= 2
ORDER BY A.EmployeeID;
這裡是，先按employeeID分組再組內又按orderdate排序。用apply...top替換
SELECT E.FirstName,
E.LastName,
OT.*
FROM Employees E
CROSS APPLY ( SELECT TOP (2) *
FROM Orders O
WHERE O.EmployeeID = E.EmployeeID
ORDER BY O.OrderDate DESC) AS OT
ORDER BY E.EmployeeID;
可以用 EXCEPT 比較下兩個查詢語句得差異。如果沒有輸出，那麼說明完全是等價的。
當可以只需要輸出一個聚合函數得值時，直接搞。例子如下：
SELECT
soh.SalesOrderID
,soh.OrderDate
,sod.max_unit_price
FROM Sales.SalesOrderHeader AS soh
JOIN
(
SELECT
max_unit_price = MAX(sod.UnitPrice),
SalesOrderID
FROM Sales.SalesOrderDetail AS sod
GROUP BY sod.SalesOrderID
) sod
ON sod.SalesOrderID = soh.SalesOrderID
這裡是關聯查詢了另外一張表 SalesOrderDetail,需要查出對應的UnitPrice得最大值。用apply...聚合函數直接替換。
SELECT
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 sod
WHERE soh.SalesOrderID = sod.SalesOrderID
) sod
那如果不是呢。
SELECT /*主外鍵*/
DISTINCT sale.WideGUID AS WideGUID, --業務GUID,
s_room.RoomNo,
s_room.Room AS RoomNum,
s_room.UnitNo,
s_room.FloorNo,
s_room.HxName AS HxName, --戶型
s_room.RoomStru AS RoomType, --房間類型
s_room.Unit AS Unit, --單元
s_room.FloorName AS Floor, --樓層·
s_room.No AS No, --號碼
s_room.RoomInfo AS RoomInfo, --房間全稱
s_room.XxDate AS XxDate, --銷許日期
s_room.JFDate AS SjjfDate, --實際交房日期
s_room.ShortRoomInfo AS ShortRoomInfo, --房間簡稱
s_room.FangPanUser AS FangPanUser, --放盤人
s_room.FangPanTime AS FangPanTime, --放盤時間
RoomControl.Reason AS FangPanReason --放盤批次
FROM ( SELECT DISTINCT s.WideGUID,
s.RoomGUID
FROM ( SELECT WideGUID,
RoomGUID
FROM s_Order
WHERE OrderTypeEnum = 0
UNION ALL
SELECT WideGUID,
RoomGuid
FROM s_Contract) s ) sale
LEFT JOIN s_room
ON s_room.RoomGUID = sale.RoomGUID
LEFT JOIN ( SELECT s_RoomControl.RoomGUID,
Reason
FROM s_RoomControl
INNER JOIN ( SELECT RoomGUID,
MAX(ControlTime) ControlTime
FROM s_RoomControl
WHERE ControlType = 0
GROUP BY RoomGUID) T
ON T.RoomGUID = s_RoomControl.RoomGUID
AND T.ControlTime = s_RoomControl.ControlTime) RoomControl
ON RoomControl.RoomGUID = s_room.RoomGUID
替換為：
SELECT /*主外鍵*/
sale.WideGUID AS WideGUID, --業務GUID,
s_room.RoomNo,
s_room.Room AS RoomNum,
s_room.UnitNo,
s_room.FloorNo,
s_room.HxName AS HxName, --戶型
s_room.RoomStru AS RoomType, --房間類型
s_room.Unit AS Unit, --單元
s_room.FloorName AS Floor, --樓層·
s_room.No AS No, --號碼
s_room.RoomInfo AS RoomInfo, --房間全稱
s_room.XxDate AS XxDate, --銷許日期
s_room.JFDate AS SjjfDate, --實際交房日期
s_room.ShortRoomInfo AS ShortRoomInfo, --房間簡稱
s_room.FangPanUser AS FangPanUser, --放盤人
s_room.FangPanTime AS FangPanTime, --放盤時間
RoomControl.Reason AS FangPanReason --放盤批次
FROM ( SELECT DISTINCT s.WideGUID,
s.RoomGUID
FROM ( SELECT WideGUID,
RoomGUID
FROM s_Order
WHERE OrderTypeEnum = 0
UNION ALL
SELECT WideGUID,
RoomGuid
FROM s_Contract) s ) sale
LEFT JOIN s_room
ON s_room.RoomGUID = sale.RoomGUID
CROSS APPLY ( SELECT TOP 1 Reason
FROM dbo.s_RoomControl
WHERE dbo.s_RoomControl.RoomGUID = dbo.s_room.RoomGUID
AND ControlType = 0
ORDER BY ControlTime DESC) RoomControl
為什麼會分析apply呢，主要是近期在工作中遇到大數據量情況下的分頁查詢，以及聚合查詢，表的數據量本來就大，再這麼一通操作，伺服器直接扛不住。通過實踐調試，最後發現apply可以解決這個問題，但是網上對這個apply也沒過多的講述，在這裡也只是做個總結。最後的建議是，如果在大數據量下有分頁查詢或是連接大表又需要聚合查詢，可以嘗試apply得寫法。可以用except來驗證兩者的輸出。
