sql apply查询应用

愿君学长松，慎勿作桃李。这篇文章主要讲述sql apply查询应用相关的知识，希望能为你提供帮助。
相关博客：
SQL中ROW_NUMBER和APPLY在处理TOP N等类似问题的一点比较
SQL Server-聚焦APPLY运算符（二十七）
你真的会玩SQL吗？冷落的Top和Apply
有以下应用场景

当用到了row_number over做分组排序时，可以考虑用apply...top替换

【sql apply查询应用】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替换

SELECTE.FirstName, E.LastName, OT.* FROMEmployees 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 /*主外键*/ DISTINCTsale.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 (SELECTs_RoomControl.RoomGUID, Reason FROMs_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 JOINs_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来验证两者的输出。