现实生活中的例子，什么时候在 SQL 中使用 OUTER / CROSS APPLY
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9275132/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Real life example, when to use OUTER / CROSS APPLY in SQL
提问by Lee Tickett
I have been looking at CROSS / OUTER APPLYwith a colleague and we're struggling to find real life examples of where to use them.
我一直在CROSS / OUTER APPLY和一位同事一起研究，我们正在努力寻找在何处使用它们的现实生活中的例子。
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).
我花了很多时间研究什么时候应该使用 Cross Apply 而不是 Inner Join？和谷歌搜索，但主要（唯一）示例似乎很奇怪（使用表中的行数来确定从另一个表中选择多少行）。
I thought this scenario may benefit from OUTER APPLY:
我认为这种情况可能受益于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 JOINwith RANK()and OUTER APPLYall seem to perform equally. I'm guessing this means the scenario isn't applicable to APPLY.
但是使用子查询、公用表表达式，OUTER JOINwithRANK()和OUTER APPLYall 似乎表现相同。我猜这意味着该场景不适用于APPLY.
Please share some real life examples and help explain the feature!
请分享一些现实生活中的例子并帮助解释该功能！
回答by Martin Smith
Some uses for APPLYare...
一些用途APPLY是...
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)重用列别名
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....
假设 1NF 违反表结构....
CREATE TABLE T
(
Id INT PRIMARY KEY,
Foo1 INT, Foo2 INT, Foo3 INT,
Bar1 INT, Bar2 INT, Bar3 INT
);
Example using 2008+ VALUESsyntax.
使用 2008+VALUES语法的示例。
SELECT Id,
Foo,
Bar
FROM T
CROSS APPLY (VALUES(Foo1, Bar1),
(Foo2, Bar2),
(Foo3, Bar3)) V(Foo, Bar);
In 2005 UNION ALLcan be used instead.
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);
回答by Sarath Avanavu
There are various situations where you cannot avoid CROSS APPLYor OUTER APPLY.
有各种情况是您无法避免的CROSS APPLY或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 JOINwith CROSS APPLY.
有很多情况下，我们需要更换INNER JOIN用CROSS APPLY。
1. If we want to join 2 tables on TOP nresults with INNER JOINfunctionality
1.如果我们想在TOP n结果上加入2个表的INNER JOIN功能
Consider if we need to select Idand Namefrom Masterand last two dates for each Idfrom Details table.
考虑我们是否需要为每个from选择Id和NamefromMaster和最后两个日期。IdDetails table
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 Idand then joined these records only in outer query on Id, which is wrong. To accomplish this, we need to use CROSS APPLY.
看，它用最后两个日期生成了最后两个日期的结果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
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 APPLYcan reference the outer table, where INNER JOINcannot do this(throws compile error). When finding the last two dates, joining is done inside CROSS APPLYie, WHERE M.ID=D.ID.
这是工作。里面的查询CROSS APPLY可以引用外表，哪里INNER JOIN做不到（抛出编译错误）。当找到最后两个日期时，加入是在CROSS APPLYie,内完成的WHERE M.ID=D.ID。
2. When we need INNER JOINfunctionality using functions.
2. 当我们需要INNER JOIN使用函数的功能时。
CROSS APPLYcan be used as a replacement with INNER JOINwhen we need to get result from Mastertable and a function.
CROSS APPLYINNER JOIN当我们需要从Mastertable 和 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 nresults with LEFT JOINfunctionality
1.如果我们想在TOP n结果上加入2个表的LEFT JOIN功能
Consider if we need to select Id and Name from Masterand last two dates for each Id from Detailstable.
考虑我们是否需要为表中的Master每个 Id选择 Id 和 Name from和最后两个日期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 Detailstable irrespective of Ideven though we join with Id. So the proper solution is using OUTER APPLY.
这会带来错误的结果，即，它会从只带来最新的两个日期数据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
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 JOINfunctionality using functions.
2. 当我们需要LEFT JOIN使用functions.
OUTER APPLYcan be used as a replacement with LEFT JOINwhen we need to get result from Mastertable and a function.
OUTER APPLYLEFT JOIN当我们需要从Mastertable 和 a 中获取结果时，可以用作替代function。
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 APPLYandOUTER APPLY
的共同特点
CROSS APPLY和OUTER APPLY
CROSS APPLYor OUTER APPLYcan be used to retain NULLvalues when unpivoting, which are interchangeable.
CROSS APPLY或OUTER APPLY可用于NULL在逆透视时保留值，这些值是可互换的。
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 UNPIVOTto bring FROMDATEAND TODATEto one column, it will eliminate NULLvalues by default.
当您使用UNPIVOT带FROMDATE和TODATE一列，它会消除NULL默认值。
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 Idnumber 3
产生以下结果。请注意，我们错过了Idnumber的记录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 APPLYor OUTER APPLYwill be useful
在这种情况下，CROSS APPLY或OUTER APPLY将是有用的
SELECT DISTINCT ID,DATES
FROM MYTABLE
OUTER APPLY(VALUES (FROMDATE),(TODATE))
COLUMNNAMES(DATES)
which forms the following result and retains Idwhere its value is 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 |
| 3 | NULL |
x------x-------------x
回答by BJury
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
回答by BJury
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.)
可以看到外层应用查询效率更高。（无法附加计划，因为我是新用户...呵呵。）
