现实生活中的例子，什么时候在 SQL 中使用 OUTER/CROSS APPLY [英] Real life example, when to use OUTER / CROSS APPLY in SQL

问题描述

我一直在与一位同事一起研究 CROSS/OUTER APPLY，我们正在努力寻找在何处使用它们的真实示例.

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.

我花了很多时间查看我什么时候应该使用 Cross Apply 而不是 Inner Join? 和谷歌搜索，但主要的(唯一的)例子似乎很奇怪(使用表中的行数来确定从另一个表中选择多少行).

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).

我认为这种情况可能会从 OUTER APPLY 中受益:

I thought this scenario may benefit from OUTER APPLY:

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

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

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

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.

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

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

推荐答案

APPLY 的一些用途是...

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

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

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) 为外部查询中的每一行调用一个表值函数

2) Calling a Table Valued Function for each row in the outer query

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 违反表结构......

Assumes 1NF violating table structure....

CREATE TABLE T
  (
     Id   INT PRIMARY KEY,

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

使用 2008+ VALUES 语法的示例.

Example using 2008+ VALUES syntax.

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

在 2005 年可以使用 UNION ALL 代替.

In 2005 UNION ALL can be used instead.

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

这篇关于现实生活中的例子，什么时候在 SQL 中使用 OUTER/CROSS APPLY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！