logo标签列表

SQL中何时使用OUTER/CROSS APPLY的实际示例

sqlsql-serversql-server-2008sql-server-2005
159

我和同事一起研究了 CROSS / OUTER APPLY，但我们很难找到真实生活中使用它们的示例。

我花了很多时间查看何时应该使用CROSS APPLY而不是INNER JOIN？并进行谷歌搜索，但主要（唯一）例子似乎相当奇怪（使用表格中的行数来确定要从另一个表格选择多少行）。

我想这种情况可能受益于OUTER APPLY：

联系人表（每个联系人包含1条记录） 通信记录表（每个联系人可以包含电话、传真、电子邮件）

但使用子查询、公共表达式、OUTER JOIN与RANK()以及OUTER APPLY似乎都能够执行得同样好。 我猜这意味着这种情况不适用于APPLY。

请分享一些真实生活中的例子，帮助解释这个功能！

5
“top n per group”或解析XML很常见。请查看我的一些答案：http://stackoverflow.com/search?tab=votes&q=user%3a27535%20%22cross%20apply%22%20or%20%22outer%20apply%22 - gbn
1
http://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/ - mehrdad
1
http://explainextended.com/2009/07/16/inner-join-vs-cross-apply/ - user330315
2
可能是当应该使用Cross Apply而不是Inner Join时？的重复问题。 - Tab Alleman
1
请查看此处 https://dev59.com/bV4c5IYBdhLWcg3wgqn2 - Sarath Subramanian
4个回答
222

APPLY 的一些用途包括...

1) 每组前 N 个查询（对于某些基数可能更有效）

SELECT pr.name,
       pa.name
FROM   sys.procedures pr
       OUTER APPLY (SELECT TOP 2 *
                    FROM   sys.parameters pa
                    WHERE  pa.object_id = pr.object_id
                    ORDER  BY pr.name) pa
ORDER  BY pr.name,
          pa.name

2) 在外部查询的每一行中调用一个表值函数

SELECT *
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)

3) 重复使用列别名 

SELECT number,
       doubled_number,
       doubled_number_plus_one
FROM master..spt_values
CROSS APPLY (SELECT 2 * CAST(number AS BIGINT)) CA1(doubled_number)  
CROSS APPLY (SELECT doubled_number + 1) CA2(doubled_number_plus_one)

4) 多组列的非规范化表格结构下的反转

假设存在违反第一正规化（1NF）的表格结构....

CREATE TABLE T
  (
     Id   INT PRIMARY KEY,

     Foo1 INT, Foo2 INT, Foo3 INT,
     Bar1 INT, Bar2 INT, Bar3 INT
  );

使用2008+ VALUES 语法的示例。

SELECT Id,
       Foo,
       Bar
FROM   T
       CROSS APPLY (VALUES(Foo1, Bar1),
                          (Foo2, Bar2),
                          (Foo3, Bar3)) V(Foo, Bar);

在2005年，可以使用UNION ALL代替。
SELECT Id,
       Foo,
       Bar
FROM   T
       CROSS APPLY (SELECT Foo1, Bar1 
                    UNION ALL
                    SELECT Foo2, Bar2 
                    UNION ALL
                    SELECT Foo3, Bar3) V(Foo, Bar);
2
那里有一个很好的用途列表，但关键是实际的例子 - 我希望能看到每个例子。 - Lee Tickett
1
对于问题 #1，可以使用排名、子查询或公共表达式来实现相同的效果。您能否提供一个例子，证明这不是真的？ - Lee Tickett
1
@LeeTickett - 请阅读链接。它有一个4页的讨论，关于何时您会更喜欢其中之一。 - Martin Smith
2
请务必访问示例＃1中包含的链接。我已经使用了这两种方法（ROW OVER和CROSS APPLY），在各种情况下都表现良好，但我从未理解它们为什么表现不同。那篇文章简直是天赐之物！专注于正确的索引匹配排序方向，在查询时对具有“适当”结构但性能问题的查询有很大帮助。谢谢您包含它！ - Chris Porter
2
@mr_eclair 看起来现在在 http://www.itprotoday.com/software-development/optimizing-top-n-group-queries。 - Martin Smith
显示剩余4条评论
142

有各种情况你无法避免使用CROSS APPLY或者OUTER APPLY。

假设你有两个表：

主表

x------x--------------------x
| Id   |        Name        |
x------x--------------------x
|  1   |          A         |
|  2   |          B         |
|  3   |          C         |
x------x--------------------x

细节表格

x------x--------------------x-------x
| Id   |      PERIOD        |   QTY |
x------x--------------------x-------x
|  1   |   2014-01-13       |   10  |
|  1   |   2014-01-11       |   15  |
|  1   |   2014-01-12       |   20  |
|  2   |   2014-01-06       |   30  |
|  2   |   2014-01-08       |   40  |
x------x--------------------x-------x



            CROSS APPLY

有很多情况需要用CROSS APPLY代替INNER JOIN。

1. 如果我们希望使用INNER JOIN的功能将2个表连接在TOP n结果上

假设我们需要从Master表中选择Id和Name，并且每个Id的最后两个日期来自Details表。

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
INNER JOIN
(
    SELECT TOP 2 ID, PERIOD,QTY 
    FROM DETAILS D      
    ORDER BY CAST(PERIOD AS DATE)DESC
)D
ON M.ID=D.ID

以上查询生成以下结果。

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
x------x---------x--------------x-------x

看到这里，它使用了最后两个日期的Id生成结果，然后仅在外部查询上将这些记录连接到Id上，这是错误的。为了解决这个问题，我们需要使用CROSS APPLY。

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
CROSS APPLY
(
    SELECT TOP 2 ID, PERIOD,QTY 
    FROM DETAILS D  
    WHERE M.ID=D.ID
    ORDER BY CAST(PERIOD AS DATE)DESC
)D

并形成以下结果。

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-08   |  40   |
|   2  |   B     | 2014-01-06   |  30   |
x------x---------x--------------x-------x

这里是工作原理。在CROSS APPLY内部的查询可以引用外部表，而INNER JOIN不能这样做（会抛出编译错误）。当查找最后两个日期时，连接是在CROSS APPLY内部完成的，即WHERE M.ID=D.ID。

2. 当我们需要使用函数实现INNER JOIN功能时。

CROSS APPLY可以用作替代方案，与INNER JOIN一起使用，当我们需要从Master表和一个function获取结果时。

SELECT M.ID,M.NAME,C.PERIOD,C.QTY
FROM MASTER M
CROSS APPLY dbo.FnGetQty(M.ID) C

这里是函数

CREATE FUNCTION FnGetQty 
(   
    @Id INT 
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT ID,PERIOD,QTY 
    FROM DETAILS
    WHERE ID=@Id
)

生成了以下结果

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-11   |  15   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-06   |  30   |
|   2  |   B     | 2014-01-08   |  40   |
x------x---------x--------------x-------x



            外部 APPLY

1. 如果我们需要在LEFT JOIN的基础上，仅针对TOP n结果进行2张表的连接

考虑我们需要从Master表选择Id和Name，并从Details表中选择每个Id的最后两个日期。

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
LEFT JOIN
(
    SELECT TOP 2 ID, PERIOD,QTY 
    FROM DETAILS D  
    ORDER BY CAST(PERIOD AS DATE)DESC
)D
ON M.ID=D.ID

生成以下结果

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     |   NULL       |  NULL |
|   3  |   C     |   NULL       |  NULL |
x------x---------x--------------x-------x

这将会带来错误的结果，即使我们使用 Id 进行连接，它只会从 Details 表中获取最新的两个日期数据。因此，正确的解决方案是使用 OUTER APPLY。

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
OUTER APPLY
(
    SELECT TOP 2 ID, PERIOD,QTY 
    FROM DETAILS D  
    WHERE M.ID=D.ID
    ORDER BY CAST(PERIOD AS DATE)DESC
)D

生成所需的结果如下：

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-08   |  40   |
|   2  |   B     | 2014-01-06   |  30   |
|   3  |   C     |   NULL       |  NULL |
x------x---------x--------------x-------x

2. 当我们需要使用函数实现 LEFT JOIN 功能时。

当我们需要从Master表和一个function获取结果时，可以使用OUTER APPLY替代LEFT JOIN。

SELECT M.ID,M.NAME,C.PERIOD,C.QTY
FROM MASTER M
OUTER APPLY dbo.FnGetQty(M.ID) C

并且函数在这里定义。

CREATE FUNCTION FnGetQty 
(   
    @Id INT 
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT ID,PERIOD,QTY 
    FROM DETAILS
    WHERE ID=@Id
)

生成了以下结果

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-11   |  15   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-06   |  30   |
|   2  |   B     | 2014-01-08   |  40   |
|   3  |   C     |   NULL       |  NULL |
x------x---------x--------------x-------x



                            CROSS APPLY 和 OUTER APPLY 的共同特点

CROSS APPLY 和 OUTER APPLY 可以用于在展开数据时保留NULL值，两者可以互换使用。

考虑下面的表格：

x------x-------------x--------------x
|  Id  |   FROMDATE  |   TODATE     |
x------x-------------x--------------x
|   1  |  2014-01-11 | 2014-01-13   | 
|   1  |  2014-02-23 | 2014-02-27   | 
|   2  |  2014-05-06 | 2014-05-30   |    
|   3  |   NULL      |   NULL       | 
x------x-------------x--------------x

使用 UNPIVOT 把 FROMDATE 和 TODATE 合并到一列时，默认会删除 NULL 值。

SELECT ID,DATES
FROM MYTABLE
UNPIVOT (DATES FOR COLS IN (FROMDATE,TODATE)) P

生成以下结果。请注意，我们错过了Id号码为3的记录。

  x------x-------------x
  | Id   |    DATES    |
  x------x-------------x
  |  1   |  2014-01-11 |
  |  1   |  2014-01-13 |
  |  1   |  2014-02-23 |
  |  1   |  2014-02-27 |
  |  2   |  2014-05-06 |
  |  2   |  2014-05-30 |
  x------x-------------x
在这种情况下，使用 CROSS APPLY 或 OUTER APPLY 是很有用的。
SELECT DISTINCT ID,DATES
FROM MYTABLE 
OUTER APPLY(VALUES (FROMDATE),(TODATE))
COLUMNNAMES(DATES)

最终结果将保留Id的值为3

  x------x-------------x
  | Id   |    DATES    |
  x------x-------------x
  |  1   |  2014-01-11 |
  |  1   |  2014-01-13 |
  |  1   |  2014-02-23 |
  |  1   |  2014-02-27 |
  |  2   |  2014-05-06 |
  |  2   |  2014-05-30 |
  |  3   |     NULL    |
  x------x-------------x
2
为什么不将其中一个标记为重复，而是在两个问题上发布完全相同的答案呢？ - Tab Alleman
7
我认为这个答案更适用于回答原问题。它的例子展示了“现实生活”中的情景。 - FrankO
1
那么澄清一下。 “前n个”场景; 可以使用左/内连接，但是使用“row_number over partition by id”，然后选择“WHERE M.RowNumber < 3”之类的东西吗？ - Chaitanya
3
总体而言，回答非常好！毫无疑问，这是比被采纳的回答更好的答案，因为它简单易懂，有方便的视觉示例和解释。 - Arsen Khachaturyan
2
非常感谢您详细和易懂的回答！ - Kushan Randima
3
我认为这应该是被接受的答案。它展示了标题所述的“现实生活”例子。 - Humayoun_Kabir
10

一个真实的例子是，如果您有一个调度程序，并想查看每个已安排任务的最新日志条目。

select t.taskName, lg.logResult, lg.lastUpdateDate
from task t
cross apply (select top 1 taskID, logResult, lastUpdateDate
             from taskLog l
             where l.taskID = t.taskID
             order by lastUpdateDate desc) lg
1
在我们的测试中，我们始终发现使用窗口函数的连接对于前n个结果最有效（我认为这总是正确的，因为应用和子查询都是递归/需要嵌套循环）。虽然我认为我现在可能已经解决了它...感谢马丁的链接，它建议如果您没有返回整个表并且表上没有最佳索引，则使用交叉应用（或子查询，如果top n其中n = 1）读取的数量将会更少。 - Lee Tickett
1
我这里基本上有那个查询，它肯定没有执行任何带有嵌套循环的子查询。鉴于日志表具有任务ID和最后更新日期的主键，这是一个非常快速的操作。您如何改写该查询以使用窗口函数？ - BJury
4
选择* 从任务表 t 内连接（选择任务ID，日志结果，最后更新日期，以 taskid 分区，按 lastupdatedate 降序排列的排名）lg 当 lg.taskid = t.taskid 且 lg._rank = 1 时。 - Lee Tickett
5
回答上面的问题，举个例子：
create table #task (taskID int identity primary key not null, taskName varchar(50) not null)
create table #log (taskID int not null, reportDate datetime not null, result varchar(50) not null, primary key(reportDate, taskId))

insert #task select 'Task 1'
insert #task select 'Task 2'
insert #task select 'Task 3'
insert #task select 'Task 4'
insert #task select 'Task 5'
insert #task select 'Task 6'

insert  #log
select  taskID, 39951 + number, 'Result text...'
from    #task
        cross join (
            select top 1000 row_number() over (order by a.id) as number from syscolumns a cross join syscolumns b cross join syscolumns c) n

现在使用执行计划运行这两个查询。

select  t.taskID, t.taskName, lg.reportDate, lg.result
from    #task t
        left join (select taskID, reportDate, result, rank() over (partition by taskID order by reportDate desc) rnk from #log) lg
            on lg.taskID = t.taskID and lg.rnk = 1

select  t.taskID, t.taskName, lg.reportDate, lg.result
from    #task t
        outer apply (   select  top 1 l.*
                        from    #log l
                        where   l.taskID = t.taskID
                        order   by reportDate desc) lg

你可以看到外部应用查询更高效。（由于我是新用户，无法附加计划...哎。）
1
执行计划很有趣-你知道为什么rank()解决方案会进行索引扫描和昂贵的排序，而不是outer apply，它进行索引搜索并且似乎没有进行排序（尽管必须进行排序，因为你不能在没有排序的情况下进行top操作）吗？ - Lee Tickett
2
外部应用程序不需要执行排序，因为它可以使用基础表上的索引。假定具有rank()函数的查询需要处理整个表以确保其排名正确。 - BJury
1
你不能在没有排序的情况下进行 TOP 操作。尽管你关于处理整个表格的观点可能是正确的，但这让我感到惊讶（我知道 SQL 优化器/编译器有时会让人失望，但这种行为太疯狂了）。 - Lee Tickett
3
当你按照索引排序时，即使没有排序操作，你仍然可以对数据进行分组并获取最前面（或最后面）的数据项，因为优化程序知道索引已经是有序的。 - BJury
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接