现实生活中的例子，何时在SQL中使用外部/交叉

I have been looking at CROSS / OUTER APPLY with a colleague and we're struggling to find real life examples of where to use them.

我一直在找一个同事，我们正在努力寻找实际生活的例子。

I've spent quite a lot of time looking at When should I use Cross Apply over Inner Join? and googling but the main (only) example seems pretty bizarre (using the rowcount from a table to determine how many rows to select from another table).

我花了很多时间来研究什么时候应该在内部连接上使用交叉应用?google一下但是主(唯一的)示例看起来很奇怪(使用表中的行数来决定从另一个表中选择多少行)。

I thought this scenario may benefit from OUTER APPLY:

我认为这种情况可以从外部应用中获益:

Contacts Table (contains 1 record for each contact) Communication Entries Table (can contain n phone, fax, email fro each contact)

通讯录表(包含每个联系人的1条记录)通讯录表(包含每个联系人的n个电话、传真、电子邮件)

But using subqueries, common table expressions, OUTER JOIN with RANK() and OUTER APPLY all seem to perform equally. I'm guessing this means the scenario isn't applicable to APPLY.

但是使用子查询、公共表表达式、RANK()外连接和OUTER APPLY似乎都具有相同的性能。我猜这意味着这个场景不适用于应用。

Please share some real life examples and help explain the feature!

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

4 个解决方案

#1

Some uses for APPLY are...

应用的一些用途是……

1) Top N per group queries (can be more efficient for some cardinalities)

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) Calling a Table Valued Function for each row in the outer query

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

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

4)取消一组以上的列

Assumes 1NF violating table structure....

假设1 nf违反表结构....

CREATE TABLE T
  (
     Id   INT PRIMARY KEY,

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

Example using 2008+ VALUES syntax.

使用2008+值语法的示例。

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

In 2005 UNION ALL can be used instead.

在2005年联盟都可以被使用代替。

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

There are various situations where you cannot avoid CROSS APPLY or OUTER APPLY.

有各种各样的情况，你不能避免交叉应用或外部应用。

Consider you have two tables.

假设您有两个表。

MASTER TABLE

掌握表

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

DETAILS TABLE

详细信息表

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

交叉应用

There are many situation where we need to replace INNER JOIN with CROSS APPLY.

在许多情况下，我们需要将内部连接替换为交叉应用。

1. If we want to join 2 tables on TOP n results with INNER JOIN functionality

1。如果我们想要连接两个表，在上面的n结果与内部连接功能。

Consider if we need to select Id and Name from Master and last two dates for each Id from Details table.

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

The above query generates the following result.

上面的查询生成如下结果。

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

See, it generated results for last two dates with last two date's Id and then joined these records only in outer query on Id, which is wrong. To accomplish this, we need to use CROSS APPLY.

看，它用最后两个日期的Id生成最后两个日期的结果，然后只在Id的外部查询中加入这些记录，这是错误的。要做到这一点，我们需要使用交叉应用。

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

and forms he following result.

并形成以下结果。

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

Here is the working. The query inside CROSS APPLY can reference the outer table, where INNER JOIN cannot do this(throws compile error). When finding the last two dates, joining is done inside CROSS APPLY ie, WHERE M.ID=D.ID.

这是工作。交叉应用中的查询可以引用外部表，内部连接不能这样做(抛出编译错误)。找到最后两个日期时，在CROSS APPLY ie中进行连接，其中M.ID=D.ID。

2. When we need INNER JOIN functionality using functions.

2。当我们需要使用函数的内部连接功能时。

CROSS APPLY can be used as a replacement with INNER JOIN when we need to get result from Master table and a function.

当我们需要从主表和函数中获得结果时，交叉应用可以作为内部连接的替换。

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

And here is the function

这是函数。

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

which generated the following result

哪个产生了以下结果?

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

                                                            OUTER APPLY

外部应用

1. If we want to join 2 tables on TOP n results with LEFT JOIN functionality

1。如果我们想要在顶部n个表上加入2个表，结果将具有左连接功能

Consider if we need to select Id and Name from Master and last two dates for each Id from Details table.

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

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

which forms the following result

哪个构成了以下的结果

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

This will bring wrong results ie, it will bring only latest two dates data from Details table irrespective of Id even though we join with Id. So the proper solution is using OUTER APPLY.

这将带来错误的结果ie，它只会带来最新的两个日期数据来自细节表，而不考虑Id，即使我们加入Id。所以正确的解决方案是使用外部应用。

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

which forms the following desired result

哪一种形式形成以下期望的结果

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. When we need LEFT JOIN functionality using functions.

2。当我们需要使用函数的左连接功能时。

OUTER APPLY can be used as a replacement with LEFT JOIN when we need to get result from Master table and a function.

当我们需要从主表和函数中获得结果时，可以使用OUTER APPLY替换左连接。

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

And the function goes here.

函数在这里。

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

which generated the following result

哪个产生了以下结果?

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

                             Common feature of CROSS APPLY and OUTER APPLY

常见的交叉应用和外部应用的特点

CROSS APPLY or OUTER APPLY can be used to retain NULL values when unpivoting, which are interchangeable.

交叉应用或外部应用可用于在不旋转时保留空值，它们是可互换的。

Consider you have the below table

假设您有下面的表

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

When you use UNPIVOT to bring FROMDATE AND TODATE to one column, it will eliminate NULL values by default.

当您使用UNPIVOT将FROMDATE和TODATE带到一列时，它将在默认情况下消除空值。

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

which generates the below result. Note that we have missed the record of Id number 3

生成下面的结果。注意，我们错过了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

In such cases a CROSS APPLY or OUTER APPLY will be useful

在这种情况下，交叉应用或外部应用将是有用的

SELECT DISTINCT ID,DATES
FROM MYTABLE 
OUTER APPLY(VALUES (FROMDATE),(TODATE))
COLUMNNAMES(DATES)

which forms the following result and retains Id where its value is 3

哪一种形式的结果并保留其值为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

#3

One real life example would be if you had a scheduler and wanted to see what the most recent log entry was for each scheduled task.

一个实际的例子是，如果您有一个调度器，并且希望查看每个调度任务的最新日志条目是什么。

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

#4

To answer the point above knock up an example:

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

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

And now run the two queries with a execution plan.

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

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

You can see that the outer apply query is more efficient. (Couldn't attach the plan as I'm a new user... Doh.)

您可以看到，外部应用查询更有效。因为我是新用户，所以不能附加这个计划。度)。