sql apply查询应用
相关博客：
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来验证两者的输出。
