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

Question

我一直在和同事一起研究CROSS/OUTER APPLY，我们正在努力寻找现实生活中如何使用它们的示例。

我花了很多时间查看When should I use CROSS APPLY over INNER JOIN?和谷歌搜索，但主要(唯一)示例似乎非常奇怪(使用表中的行计数来确定从另一个表中选择多少行)。

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

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

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

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

Answer 1

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