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

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

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

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

customer:

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

 

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

Sql Server中不常用的表运算符之APPLY(2)
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
Sql Server中不常用的表运算符之APPLY(2)

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

Sql Server中不常用的表运算符之APPLY(2)
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
Sql Server中不常用的表运算符之APPLY(2)

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

Sql Server中不常用的表运算符之APPLY(2)
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
Sql Server中不常用的表运算符之APPLY(2)

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

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

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

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

Sql Server中不常用的表运算符之APPLY(2)
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
);
Sql Server中不常用的表运算符之APPLY(2)

 

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

Sql Server中不常用的表运算符之APPLY(2)
SELECT TopNOrder.OrderId,c.CustomName FROM 
  dbo.Customer AS c
    CROSS APPLY dbo.TopNOrder(2,c.CustomId) AS TopNOrder
Sql Server中不常用的表运算符之APPLY(2)

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

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

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

Sql Server中不常用的表运算符之APPLY(2)
SELECT TopNOrder.OrderId,c.CustomName FROM 
  dbo.Customer AS c
    OUTER APPLY dbo.TopNOrder(2,c.CustomId) AS TopNOrder
Sql Server中不常用的表运算符之APPLY(2)

使用OUTER APPLY的结果为

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

最后,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

Sql Server中不常用的表运算符之APPLY(2),布布扣,bubuko.com

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

上一篇:自定义脚本使nagios监控window的DNS服务


下一篇:POJ 3321 Apple Tree