现实生活中的例子，什么时候在 SQL 中使用 OUTER/CROSS APPY

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

我花了很多时间查看 我什么时候应该在内接头上使用交叉应用？并在谷歌上搜索，但主要(唯一)示例似乎非常奇怪(使用表中的行数来确定从另一个表中选择多少行)。

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

联系人表(包含每个联系人的1条记录) 沟通条目表(可以包含 n 个电话，传真，电子邮件的每个联系人)

但是使用子查询、公共表表达式、 OUTER JOIN和 RANK()以及 OUTER APPLY的性能似乎都是一样的。我猜这意味着这个场景不适用于 APPLY。

请分享一些现实生活中的例子，并帮助解释这个特性！

231537 次浏览
小开
最佳答案

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);
小开

现实生活中的一个例子是，如果您有一个调度程序，并希望查看每个计划任务的最新日志条目。

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
小开

为了回答上面的问题，举个例子:

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

您可以看到外部应用程序查询更有效。(无法附上计划，因为我是一个新用户... ... 噢。)

小开

有各种各样的情况下，你不能避免 CROSS APPLY或 OUTER APPLY。 < br >

考虑到你有两张桌子。

主桌

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代替 INNER JOIN。

1. 如果我们想在 TOP n结果上连接两个具有 INNER JOIN功能的表

考虑是否需要从 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. 如果我们想在 TOP n结果上连接两个具有 LEFT JOIN功能的表

考虑是否需要从 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



ABC0和 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)

它形成以下结果并保留其值为 3的 Id

  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

提交答案