SQL Server-聚焦APPLY运算符

初探APPLY运算符

APPLY运算符是一个非常强大的表运算符，但是APPLY不是标准的，相对应的标准叫做LATERAL，但是此标准并未在SQL Server中实现。像所有表运算符一样，该运算符用于查询的FROM子句中。APPLY运算符支持的类型是CROSS APPLY和OUTER APPLY。CROSS APPY仅仅实施一个逻辑查询处理阶段，而OUTER APPLY实施了两个阶段，APPLY运算符对两个输入表进行操作，第二个可以是一个表表达式，我们将APPLY两侧的表分别叫做左侧表和右侧表，右侧表通常是一个派生表或TVF（内嵌表值函数）。CROSS APPLY运算符实施一个逻辑查询处理阶段-它将右侧的表表达式应用到左侧表的每一行，并生成一个组合结果集的结果表。CROSS APPLYl类似于交叉联接中的CROSS JOIN，但是使用CROSS APPLY运算符，右侧的表表达式可以对来自左侧表的每一行表示一个不同的行集，这是与联接的不同之处。当在右侧使用一个派生表，并且派生表查询中引用来自左侧表的属性，就可以实现此目标，或者是在右侧使用一个内嵌TVF，可以传递左侧的属性作为输入参数，同样可以实现此目的-摘抄自SQL Server 2012基础教程。下面我们看一个简单的例子。

USE TSQL2012
GO

SELECT C.custid, A.orderid, A.orderdate
FROM Sales.Customers AS C
    CROSS APPLY
        (SELECT TOP(3) orderid, empid, orderdate, requireddate 
        FROM Sales.Orders AS O
        WHERE O.custid = C.custid
        ORDER BY orderdate DESC, orderid DESC) AS A;

上述完成的是返回每个客户最近的3个订单。我们可以将右侧的表表达式看做是一个相关子查询，右侧的表表达式通过引用custid对来自Customers表的每一行进行处理并返回每个客户的最近的3个订单，是不是看起来很清爽呢，下面我们将进一步探讨APPLY运算符的作用。

进一步探讨APPLY运算符

上面我们看到通过相关子查询来进行查询显得代码有点丑陋，我们再来看一个例子。查询每个单价最高的订单，我们通过子查询来实现。

CROSS APPLY

USE AdventureWorks2012
GO

SELECT 
     SalesOrderID
    ,OrderDate
    ,MaxUnitPrice =(SELECT MAX(sod.UnitPrice) FROM Sales.SalesOrderDetail sod WHERE soh.SalesOrderID = sod.SalesOrderID)
FROM Sales.SalesOrderHeader AS soh

如上操作看似代码比较简洁也能完成我们的查询诉求，但是我们用派生表来进行查询又是怎样的呢？ 

USE AdventureWorks2012
GO

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

此时由于两个表完全不相关，我们需要通过GROUP BY完成再进行JOIN，代码不是显得非常臃肿吗，这还是简单的，当有多个表时就比较复杂了，导致代码就不再具有可读性。但是自从在SQL Server 2005中有了APPLY妈妈再也不用担心我读不懂复杂的代码了，我们看看CROSS APPLY是怎样实现的。

USE AdventureWorks2012
GO

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

当我们利用内部联接时此时JOIN中的查询是独立的所以需要进行GROUP BY，而对于CROSS APPLY它本身就是对来自左侧的表中每一行就行处理并返回，同时利用CROSS APPLY它也超越了相关子查询，比如说我们还需要查出每个订单的总价呢，我们利用相关子查询需要再次嵌入SELECT子句。

SELECT 
     SalesOrderID           
    ,OrderDate              
    ,MaxUnitPrice           = (SELECT MAX(sod.UnitPrice) FROM Sales.SalesOrderDetail sod WHERE soh.SalesOrderID = sod.SalesOrderID)
    ,SumLineTotal           = (SELECT SUM(LineTotal) FROM Sales.SalesOrderDetail sod WHERE soh.SalesOrderID = sod.SalesOrderID)
FROM Sales.SalesOrderHeader AS soh

而利用CROSS APPLY只需添加集合函数SUM即可

USE AdventureWorks2012
GO

SELECT 
    soh.SalesOrderID
    ,soh.OrderDate
    ,sod.max_unit_price
    ,sod.sum_line_total
FROM Sales.SalesOrderHeader AS soh
CROSS APPLY
(
    SELECT 
        max_unit_price = MAX(sod.UnitPrice)
        ,sum_line_total = SUM(sod.LineTotal)
    FROM Sales.SalesOrderDetail AS sod
    WHERE soh.SalesOrderID = sod.SalesOrderID
) sod

OUTER APPLY

对于OUTER APPLY，如果右侧的表表达式返回一个空集合，CROSS APPLY运算符不会返回相应的左侧行，也就是说OUTER APPLY和在派生表上进行LEFT JOIN是等同的，如下：

SELECT 
    soh.SalesOrderID
    ,soh.OrderDate
    ,sod.max_unit_price
FROM Sales.SalesOrderHeader AS soh
LEFT JOIN
(
    SELECT 
        max_unit_price = MAX(sod.UnitPrice),
        SalesOrderID
    FROM Sales.SalesOrderDetail AS sod
    GROUP BY sod.SalesOrderID
) sod
ON sod.SalesOrderID = soh.SalesOrderID

此时我们利用OUTER APPLY则是如下：

USE AdventureWorks2012
GO

SELECT 
    soh.SalesOrderID
    ,soh.OrderDate
    ,sod.max_unit_price
FROM Sales.SalesOrderHeader AS soh
OUTER APPLY
(
    SELECT 
        max_unit_price = MAX(sod.UnitPrice)
    FROM Sales.SalesOrderDetail AS sod
    WHERE soh.SalesOrderID = sod.SalesOrderID
) sod

上述对于APPLY右侧表表达式是一个派生表，此时为了封装，我们可以使用TVF内嵌表值函数来实现。其实将内嵌表值函数来代替派生表实现每个客户最近的3个订单。首先我们封装一个表值函数

USE TSQL2012
GO

IF OBJECT_ID('dbo.TopOrders') IS NOT NULL
    DROP FUNCTION dbo.TopOrders;
GO

CREATE FUNCTION dbo.TopOrders
    (@custid  AS INT, @n  AS  INT)
    RETURNS TABLE
AS RETURN

    SELECT  orderid, empid, orderdate, requireddate
    FROM Sales.Orders
    WHERE  custid = @custid
    ORDER BY orderdate DESC, orderid DESC
    OFFSET 0 ROWS FETCH FIRST @n ROWS ONLY;
GO

接着利用CROSS APPLY进行查询。

USE TSQL2012
GO

SELECT C.custid, C.companyname, A.orderid, A.empid, A.requireddate
FROM Sales.Customers AS C
 CROSS APPLY dbo.TopOrders(C.custid, 3) AS A;

上面我们通过封装内嵌表值函数代替派生表使代码更具可读性和可维护性。到此我们可以得出一点基本结论。

APPLY运算符使用分析结论：当需要对表中的每一行进行应用时，且需要将所有结果集组合到一个结果集表中时，此时我们应该使用APPLY运算符，至于是使用CROSS APPLY还是OUTER APPLY根据场景而定，虽然APPLY右侧表可以用相关子查询或者派生表来实现，但是使得代码臃肿和可维护性差，通过封装内嵌表值函数来实现可以说是对右侧表通过相关子查询或者派生表来实现的完美替代者。