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

2022-01-31 00:00:00 sql sql-server-2005 sql-server-2008 sql-server

我一直在与一位同事一起研究 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 条记录)通讯条目表(可以包含每个联系人的电话、传真、电子邮件)

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

相关文章