现实生活中的例子，何时在 SQL 中使用 OUTER / CROSS APPLY

sql sql-server sql-server-2008 sql-server-2005

我一直在和一位同事一起研究 CROSS / OUTER APPLY，我们正在努力寻找在哪里使用它们的真实示例。

我花了很多时间查看 When should I use Cross Apply over Inner Join? 和谷歌搜索，但主要（唯一）示例似乎很奇怪（使用表中的行数来确定从另一个表中选择多少行）。

我认为这种情况可能会受益于 OUTER APPLY：

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

但使用子查询、公用表表达式、OUTER JOIN 与 RANK() 和 OUTER APPLY 似乎都执行相同。我猜这意味着该场景不适用于 APPLY。

请分享一些现实生活中的例子并帮助解释该功能！

“每组前 n 个”或解析 XML 很常见。查看我的一些答案stackoverflow.com/…

mssqltips.com/sqlservertip/1958/…

explainextended.com/2009/07/16/inner-join-vs-cross-apply

When should I use Cross Apply over Inner Join? 的可能重复项

在这里也检查一下stackoverflow.com/questions/27838045/where-to-use-outer-apply

Martin Smith

APPLY 的一些用途是...

1) Top N per group queries（对于某些基数可能更有效）

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) Reusing a column alias

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) Unpivoting more than one group of columns

假设 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);

那里有一个很好的用途列表，但关键是现实生活中的例子——我很想看到一个。

对于#1，这可以通过排名、子查询或公用表表达式来实现吗？当这不是真的时，你能提供一个例子吗？

@LeeTickett - 请阅读链接。它有一个 4 页的讨论，关于您何时更喜欢另一个。

确保访问示例 #1 中包含的链接。我已经使用了这两种方法（ROW OVER 和 CROSS APPLY），它们在各种场景中都表现良好，但我一直不明白为什么它们的表现不同。那篇文章是天上掉下来的！！专注于按方向匹配顺序的正确索引极大地帮助了具有“正确”结构但在查询时出现性能问题的查询。谢谢你把它包括在内！！

@mr_eclair 现在好像在 itprotoday.com/software-development/…

Sarath Subramanian

在各种情况下，您无法避免 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

交叉申请

在很多情况下，我们需要将 INNER JOIN 替换为 CROSS APPLY。

<强> 1。如果我们想使用 INNER JOIN 功能在 TOP n 结果中加入 2 个表

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

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 功能时。

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

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

外用

<强> 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

这将带来错误的结果，即它只会从 Details 表中带来最新的两个日期数据，而与 Id 无关，即使我们加入了 Id。所以正确的解决方案是使用 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。当我们需要使用 functions 的 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 AND 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

为什么不将一个问题标记为重复，而不是在两个问题上发布完全相同的答案？

我发现这个答案更适用于回答原始问题。它的例子展示了“现实生活”的场景。

总体来说很好的答案！当然，这是一个比公认的更好的答案，因为它是：简单，带有方便的视觉示例和解释。

非常感谢您详细而易懂的回答！

我认为这应该是公认的答案。它显示了标题所说的“现实生活”示例。

BJury

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

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

在我们的测试中，我们总是发现使用窗口函数连接对 top n 最有效（我认为这总是正确的，因为应用和子查询都是草书/需要嵌套循环）。尽管我认为我现在可能已经破解了它...感谢 Martin 的链接，该链接表明如果您没有返回整个表并且表上没有最佳索引，那么使用交叉应用（或如果 top n 其中 n = 1，则为子查询）

我基本上在这里得到了那个查询，它肯定不会执行任何带有嵌套循环的子查询。鉴于日志表有一个 taskID 和 lastUpdateDate 的 PK，它是一个非常快速的操作。您将如何修改该查询以使用窗口函数？

select * from task t inner join (select taskid, logresult, lastupdatedate, rank() over(partition by taskid order by lastupdatedate desc) _rank) lg on lg.taskid = t.taskid and lg._rank = 1

BJury

要回答上述问题，请举一个例子：

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

可以看到外部apply查询效率更高。（因为我是新用户，所以无法附上计划……Doh。）

执行计划让我感兴趣 - 你知道为什么 rank() 解决方案进行索引扫描和昂贵的排序，而不是外部应用，它进行索引搜索并且似乎没有进行排序（尽管它必须是因为你可以' t 做一个没有排序的上衣？）

外部应用不需要执行排序，因为它可以使用基础表上的索引。大概使用 rank() 函数的查询需要处理整个表以确保其排名正确。

没有排序你不能做一个顶部。尽管您关于处理整个表的观点可能是真的，但这会让我感到惊讶（我知道 sql 优化器/编译器有时会令人失望，但这将是疯狂的行为）

当您分组的数据针对索引时，您可以在没有排序的情况下置顶，因为优化器知道它已经排序，因此实际上只需要从索引中拉出第一个（或最后一个）条目。

现实生活中的例子，何时在 SQL 中使用 OUTER / CROSS APPLY

关注公众号

想领先一步获取最新的外包任务吗？

相似问题

平台

支持

友情链接

联系我们