sql apply查询应用

相关博客：

SQL中ROW_NUMBER和APPLY在处理TOP N等类似问题的一点比较

SQL Server-聚焦APPLY运算符（二十七）

你真的会玩SQL吗？冷落的Top和Apply

有以下应用场景

• 当用到了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来验证两者的输出。