Cross Apply、Outer Apply 的一些独特场景。
5.00/5 (1投票)
深入了解 CROSS APPLY 和 OUTER APPLY
引言
在处理复杂/嵌套查询中的计算字段时，可以使用 CROSS APPLY 和 OUTER APPLY，这可以使它们更简单、更易读，而这对于传统连接来说可能具有挑战性。Cross Apply 的工作方式类似于游标，一次处理一条记录，而不是像连接那样进行集合操作。在大多数情况下，APPLY 的性能与连接相当，但在某些情况下，它可能会变慢。在本文中，我将讨论一些可以使用 APPLY 子句的场景。
没有 Cross Apply 和 Outer Apply，我们的选择有限
- CROSS APPLY 语句的行为类似于相关子查询，但允许我们在子查询中使用 ORDER BY 语句。当我们需要从子查询中提取顶部记录以供外部查询使用时，这非常有用。
- SQL Server 用户定义函数倾向于阻止并行操作。它不会内联函数。随着 SQL 2022 的每次累积更新的发布，内联发生的限制越来越多。
- 中间表 - 通常有效，并且通常是一个不错的选择，因为它们可以被索引并且速度快，但由于 UPDATE 语句不是并行的，并且不允许级联或重用结果来更新同一语句中的多个字段，因此性能也可能会下降。连接表可以工作，但很快你就会将子查询与许多 UNION ALL 语句连接起来。
- 嵌套查询和重复代码 - 您可以将括号放在查询中，并将其用作子查询，然后您可以操作源数据和计算字段。它不能包含 COMPUTE 或 FOR BROWSE 子句，并且只有在同时指定了 TOP 子句时才能包含 ORDER BY 子句。
- 通过 CROSS APPLY 引入的值可用于创建一条或多条计算字段，而不会增加性能或复杂性，因此 CROSS APPLY 类似于 INNER JOIN，或者更确切地说，类似于具有隐式连接条件 1=1 的相关子查询的 CROSS JOIN。
派生值
此示例显示了如何对派生值链执行“过程式”计算。JOIN 子查询不能基于父查询中的数据在数据集中引入新值（它必须独立存在）。
SELECT calc1.val1,
ROW_NUMBER() OVER (PARTITION BY calc1.val1 ORDER BY calc5.price_gross) pos_number,
calc1.price1,
calc2.price2,
calc3.price3,
calc4.price4,
calc5.price_gross
FROM tbl t
CROSS APPLY (SELECT CASE t.col1 WHEN 1 THEN 'A' WHEN 2 THEN 'B' WHEN 3 THEN 'C' END val1, t.price * (100 - t.discount1) / 100 AS price1) as calc1
CROSS APPLY (SELECT calc1.price1 * (100 - t.discount2) / 100 AS price2) as calc2
CROSS APPLY (SELECT calc2.price2 * (100 - t.discount3) / 100 AS price3) as calc3
CROSS APPLY (SELECT calc3.price3 * (100 - t.discount4) / 100 AS price4) as calc4
CROSS APPLY (SELECT calc4.price4 * (100 + t.VAT) / 100 AS price_gross) as calc5
INNER JOIN tbl2 t2 ON t2.val1 = calc1.val1
ORDER BY calc1.val1
Top N 值
另一个用途是，假设您有两个表，Customer 和 Order。Customer 有许多 Order。如果我想创建一个视图，其中包含客户的详细信息以及他们最近的 10 个订单。仅使用 JOINS，这将需要一些自连接和聚合，这并不理想。但是使用 Cross Apply，它非常简单且易于查看。
SELECT * FROM Customer cust CROSS APPLY ( SELECT TOP 10 * FROM Order o WHERE o.CustomerId = cust.CustomerId ORDER BY OrderDate DESC ) T
DRY 原则
CROSS APPLY 可用于防止重复代码，当我在查询的多个部分必须使用相同的 CASE 语句时。此外，当您有交错的计算，其中第二个值取决于第一个结果，第三个使用第二个结果，第四个使用第三个结果，依此类推时，使用多个 CROSS APPLY 非常方便。
SELECT
webTable.*,
LEFT(noPrefix.myURL, domain_end.pos) AS domain
FROM webTable
CROSS APPLY
(SELECT REPLACE(webTable.myURL, 'www.', '') AS myURL) AS [noPrefix]
CROSS APPLY
(SELECT CHARINDEX('.', noPrefix.MyURL) AS pos) AS [suffix_start]
CROSS APPLY
(SELECT CASE WHEN suffix_start.pos = 0 THEN LEN(noPrefix.myURL) ELSE suffix_start.pos - 1 END AS pos) AS [domain_end]
使用表值函数进行内连接
cross apply 实际上是一个交叉乘积运算符。CROSS APPLY 运算符仅返回左表表达式中与右表表达式匹配的行，这并不是全部。它碰巧充当内连接和外连接。 Cross apply 有时可以实现使用 inner join 无法实现的功能。
下面的查询将引发语法错误
SELECT fn.* from sys.objects sobj INNER JOIN dbo.myTableFunc(sobj.name) fn ON fn.schema_id = sobj.schema_id
这是一个 语法错误，因为当与 inner join 一起使用时，表函数只能接受 变量或常量 作为参数。也就是说，表函数的参数不能依赖于另一个表的列。
但是，我们可以使用 CROSS Apply 引用表别名列名
SELECT fn.* from sys.objects sobj CROSS APPLY dbo.myTableFunc(sobj.name) fn WHERE fn.schema_id= sobj.schema_id
XML 字段
CROSS APPLY 在允许一个集合依赖于另一个集合方面有其明显的用途（与 JOIN 运算符不同），但这并非没有代价：它的行为就像一个对左集中的每个成员进行操作的函数，因此，在 SQL Server 中，它总是执行 Loop Join ，这通常不是连接集合的最佳方式。因此，在需要时使用 APPLY ，但不要过度使用它，以免取代 JOIN 。下面是一个简单的订单 XML 片段，我们使用 CROSS Apply 将 OrderDetail 联接到 Order xml。
DECLARE @x1 xml =
'<Orders>
<Order>
<OrderID>13000</OrderID>
<CustomerID>ALFKI</CustomerID>
<OrderDate>2006-09-20Z</OrderDate>
<EmployeeID>2</EmployeeID>
</Order>
<Order>
<OrderID>13001</OrderID>
<CustomerID>VINET</CustomerID>
<OrderDate>2006-09-20Z</OrderDate>
<EmployeeID>1</EmployeeID>
</Order>
</Orders>'
DECLARE @x2 xml =
'<Orders>
<OrderDetails>
<OrderID>13000</OrderID>
<ProductID>76</ProductID>
<Price>123</Price>
<Qty>10</Qty>
</OrderDetails>
<OrderDetails>
<OrderID>13000</OrderID>
<ProductID>16</ProductID>
<Price>3.23</Price>
<Qty>20</Qty>
</OrderDetails>
</Orders>'
以及将这两个 xml 连接起来的查询
SELECT a.OrderID,c.ProductID,c.Price,c.Qty FROM
(SELECT OrderID = O.n.value('(OrderID/text())[1]', 'int'),
CustomerID = O.n.value('(CustomerID/text())[1]', 'nchar(5)'),
OrderDate = O.n.value('(OrderDate/text())[1]', 'datetime'),
EmployeeId = O.n.value('(EmployeeID/text())[1]', 'smallint')
FROM @x1.nodes('/Orders/Order') AS O(n)) a
CROSS APPLY
(
SELECT * FROM (
SELECT OrderID = D.n.value('(OrderID/text())[1]', 'int'),
ProductID = D.n.value('(ProductID/text())[1]', 'int'),
Price = D.n.value('(Price/text())[1]', 'decimal(10,2)'),
Qty = D.n.value('(Qty/text())[1]', 'int')
FROM @x2.nodes('/Orders/OrderDetails') AS D(n)) b
WHERE a.OrderID=b.OrderID
) c
UNPIVOT 的替代方法
APPLY 可以用作 UNPIVOT 的替代品。这里可以使用 CROSS APPLY 或 OUTER APPLY ，它们是可互换的。
考虑您有下表。
CREATE TABLE tblOrders (OrderId int identity, DVD int, HardDisk int, RAM int)
INSERT INTO tblOrders
SELECT 1, 3, 3
UNION ALL
SELECT 2, 5, 4
UNION ALL
SELECT 1, 3, 10
/******************************************************************/
SELECT DISTINCT OrderId, ProductName, ProductQty
FROM tblOrders
CROSS APPLY(VALUES ('DVD', DVD),('HardDisk', HardDisk), ('RAM', RAM))
COLUMNNAMES(ProductName, ProductQty)
使用 Cross Apply 进行数据透视操作的结果。
OrderId ProductName ProductQty ----------- ----------- ----------- 1 DVD 1 1 HardDisk 3 1 RAM 3 2 DVD 2 2 HardDisk 5 2 RAM 4 3 DVD 1 3 HardDisk 3 3 RAM 10
性能 - 公用表表达式与 Cross Apply
无论您使用 APPLY、IN 还是 EXISTS，查询优化器都不会逐字解读查询文本。它会将您的代码转换为逻辑操作，并在此基础上进行优化，使用各种转换。可以用多种方式编写相同的逻辑需求，并且它们通常会产生相同或微小差异的执行计划。在所有条件相同的情况下，相同的执行计划会产生相同的结果。
使用两个子查询
SELECT
(
SELECT MAX(to1.Qty1) as mQty1
FROM tblOrder to1
WHERE to1.OrderId = orderDetail.Id
),
(
SELECT MAX(to2.Qty2) as mQty2
FROM tblOrder to2
WHERE to2.OrderId = orderDetail.Id
)
FROM tblDetails d
使用 Cross Apply 进行相同操作
SELECT t.mQty1, t.mQty2
FROM tblDetails d
CROSS APPLY
(
SELECT MAX(to1.Qty1) as mQty1, MAX(to1.Qty2) as mQty2
FROM tblOrder to1
WHERE to1.OrderId = orderDetail.Id
) as t
通过 Apply 运算符 cross 或 outer，您将能够编写一个返回与使用子查询的查询相同结果的查询。但这两种写查询的方式有什么区别呢？您可能已经注意到，在第一个查询中，有必要使用两个子查询，而使用 cross apply 时，可以只读取一次 tblOrder 表中的数据。
优点是，cross apply 实际上读取的 tblOrder 表的页面数量减半。但缺点是 SQL Server 会在 tempdb 中创建一个临时表，其中包含 Cross Apply 查询返回的数据。使用 tempdb 可能会影响性能。然而，如果子查询的数量增加，性能很可能会转向 cross apply。