Sql Server中不常用的表运算符之APPLY(2)

在Sql Server中不常用的表运算符之APPLY(1)中提到，SQL2005中新支持的APPLY的特性：
1.可以直接将表表达式(表值函数或者子查询)作为APPLY语句的右表连接左表。
2.由于使用APPLY是总是先计算左表达式表，所以右表表达式可以使用左表表达式中的列。
3.APPLY主要用于将表值函数运用在右表表达式中表的每一行。

有时也APPLY将一个子查询作为左表表达式。假设我们有以下的两张表

order:

customer:

 

选出customer C001最近的N个订单可以使用inner join

SELECT TOP (N) CustomName,OrderId,CreateTime
    FROM dbo.Customer c
        INNER JOIN dbo.[Order] o
            ON o.CustomId=c.CustomId
            AND o.CustomId='C001'
    ORDER BY o.CreateTime DESC

但是要选出每个 customer最近的N个订单（TOP N per Group problem）单单靠inner
join就无能为力了，但是通过CTE和ROW_NUMBER()的辅助可以解决这个问题

WITH OrderedOrders AS 
( 
SELECT c.CustomName
       ,o.OrderId
       ,ROW_NUMBER() OVER (PARTITION BY c.CustomId ORDER BY o.CreateTime DESC) AS CustomOrder 
    FROM dbo.[Order] AS o
        INNER JOIN dbo.Customer AS c
        ON o.CustomId = c.CustomId
) 
SELECT o1.OrderId,o2.CustomName
    FROM dbo.[Order] AS o1
        INNER JOIN OrderedOrders AS o2 
        ON o1.OrderId = o2.OrderId
WHERE o2.CustomOrder <= N

这个查询比较难读，而且不自然。下面来看看使用CROSS APPLY的版本

SELECT TopNOrder.OrderId,c.CustomName
    FROM dbo.Customer AS c
        CROSS APPLY(
            SELECT TOP (N) o.OrderId
                FROM dbo.[Order] o
                    WHERE c.CustomId=o.CustomId
                ORDER BY o.CreateTime DESC    
) AS TopNOrder

使用APPLY让这个查询看起来更加的自然。
而更自然的方式正如http://technet.microsoft.com/zh-cn/library/ms175156(v=sql.105).aspx提到的

“使用APLLY运算符可以为实现查询操作的外部表表达式返回的每个行调用表值函数。表值函数作为右输入，外部表表达式作为左输入。”

我们应该将TOP N的逻辑放在一个表值函数中，通过APPLY将这个表值函数运用在左输入中的每一行。

下面来创建一个内联的SQL Server函数

CREATE FUNCTION dbo.TopNOrder(@N int,@customerID varchar(50)) RETURNS TABLE 
AS RETURN
( 
SELECT TOP (@N) o.OrderId
　　FROM dbo.[Order] AS o
　　　　WHERE o.CustomId=@customerID
　　ORDER BY o.CreateTime DESC
);

 

然后我们可以这样使用，将返回和上面两个查询一样的结果

SELECT TopNOrder.OrderId,c.CustomName FROM 
　　dbo.Customer AS c
　　　　CROSS APPLY dbo.TopNOrder(2,c.CustomId) AS TopNOrder

以上查询当N为2的结果为

此外APPLY还可以用OUTTER来修饰，效果类似于LEFT JOIN。
运行下面这个查询，将没有数据的customer Koo

SELECT TopNOrder.OrderId,c.CustomName FROM 
　　dbo.Customer AS c
　　　　OUTER APPLY dbo.TopNOrder(2,c.CustomId) AS TopNOrder

使用OUTER APPLY的结果为

最后，APLLY还有更多可以发掘的用法，在某些时候，可以用APPLY来处理复杂的查询和提高查询的性能。但暂时先在这里告一段落。
详情可以查看以下链接：
http://sqlmag.com/sql-server-2005/sql-server-2005s-apply-part-1
http://sqlmag.com/sql-server-2005/sql-server-2005s-apply-part-2
http://sqlblog.com/blogs/rob_farley/archive/2011/04/13/the-power-of-t-sql-s-apply-operator.aspx