SQLServer CROSS APPLY使用

在 SQL Server 中，CROSS APPLY 是一个用于将一个表值函数或子查询与一个表相关联的运算符。它类似于其他数据库系统中的 OUTER APPLY 运算符，但返回所有行，即使子查询结果为空。

使用 CROSS APPLY 可以将一个表值函数或子查询应用于另一个表或结果集的每一行，并返回一个结果集。这个结果集将与原始表或结果集的每一行相关联。

以下是一个简单的示例，演示如何使用 CROSS APPLY：

假设我们有一个名为 Employees 的表，其中包含员工的姓名和部门信息。我们还有一个名为 Departments 的表，其中包含部门的名称和描述。我们想要获取每个员工的部门名称和描述。

SELECT E.EmployeeName, D.DepartmentName, D.DepartmentDescription  
FROM Employees E  
CROSS APPLY (  
    SELECT DepartmentName, DepartmentDescription  
    FROM Departments  
    WHERE DepartmentID = E.DepartmentID  
) D;

在这个示例中，CROSS APPLY 将 Departments 表与 Employees 表相关联，并返回每个员工的部门名称和描述。如果某个员工没有部门（即 DepartmentID 为 NULL），则子查询将返回空结果集，但由于是 CROSS APPLY，原始表的所有行仍然会被返回，但与部门相关的列将为 NULL。

总之，CROSS APPLY 用于将一个表值函数或子查询应用于另一个表的每一行，并返回一个与原始表相关联的结果集。

使用实例代码:

with cte as
(
select *,
cast(substring(month_info,7,3) as int) month_index --相对new_refreshdate的月份差值,用于计算具体的yyyymm
from (select new_totalreturnplanId,new_refreshdate,
ownerid,
isnull(convert(char(10) ,new_collectiondate_n, 120),'')+'|'+isnull(cast(new_collectionamount_n as varchar),'')+'|'+isnull(cast(new_collectionamount_org_n as varchar),'') month_0,
isnull(convert(char(10) ,new_collectiondate_n1, 120),'')+'|'+isnull(cast(new_collectionamount_n1 as varchar),'')+'|'+isnull(cast(new_collectionamount_org_n1 as varchar),'') month_1,
isnull(convert(char(10) ,new_collectiondate_n2, 120),'')+'|'+isnull(cast(new_collectionamount_n2 as varchar),'')+'|'+isnull(cast(new_collectionamount_org_n2 as varchar),'') month_2,
isnull(convert(char(10) ,new_collectiondate_n3, 120),'')+'|'+isnull(cast(new_collectionamount_n3 as varchar),'')+'|'+isnull(cast(new_collectionamount_org_n3 as varchar),'') month_3,
isnull(convert(char(10) ,new_collectiondate_n4, 120),'')+'|'+isnull(cast(new_collectionamount_n4 as varchar),'')+'|'+isnull(cast(new_collectionamount_org_n4 as varchar),'') month_4,
isnull(convert(char(10) ,new_collectiondate_n5, 120),'')+'|'+isnull(cast(new_collectionamount_n5 as varchar),'')+'|'+isnull(cast(new_collectionamount_org_n5 as varchar),'') month_5,
isnull(convert(char(10) ,new_collectiondate_n6, 120),'')+'|'+isnull(cast(new_collectionamount_n6 as varchar),'')+'|'+isnull(cast(new_collectionamount_org_n6 as varchar),'') month_6,
isnull(convert(char(10) ,new_collectiondate_n7, 120),'')+'|'+isnull(cast(new_collectionamount_n7 as varchar),'')+'|'+isnull(cast(new_collectionamount_org_n7 as varchar),'') month_7,
isnull(convert(char(10) ,new_collectiondate_n8, 120),'')+'|'+isnull(cast(new_collectionamount_n8 as varchar),'')+'|'+isnull(cast(new_collectionamount_org_n8 as varchar),'') month_8,
isnull(convert(char(10) ,new_collectiondate_n9, 120),'')+'|'+isnull(cast(new_collectionamount_n9 as varchar),'')+'|'+isnull(cast(new_collectionamount_org_n9 as varchar),'') month_9,
isnull(convert(char(10) ,new_collectiondate_n10, 120),'')+'|'+isnull(cast(new_collectionamount_n10 as varchar),'')+'|'+isnull(cast(new_collectionamount_org_n10 as varchar),'') month_10,
isnull(convert(char(10) ,new_collectiondate_n11, 120),'')+'|'+isnull(cast(new_collectionamount_n11 as varchar),'')+'|'+isnull(cast(new_collectionamount_org_n11 as varchar),'') month_11,
isnull(convert(char(10) ,new_collectiondate_n12, 120),'')+'|'+isnull(cast(new_collectionamount_n12 as varchar),'')+'|'+isnull(cast(new_collectionamount_org_n12 as varchar),'') month_12,
isnull(convert(char(10) ,new_collectiondate_n13, 120),'')+'|'+isnull(cast(new_collectionamount_n13 as varchar),'')+'|'+isnull(cast(new_collectionamount_org_n13 as varchar),'') month_13,
isnull(convert(char(10) ,new_collectiondate_n14, 120),'')+'|'+isnull(cast(new_collectionamount_n14 as varchar),'')+'|'+isnull(cast(new_collectionamount_org_n14 as varchar),'') month_14,
isnull(convert(char(10) ,new_collectiondate_n15, 120),'')+'|'+isnull(cast(new_collectionamount_n15 as varchar),'')+'|'+isnull(cast(new_collectionamount_org_n15 as varchar),'') month_15
from new_totalreturnplanBase) SourceTable  --1.先把字段拼接起来 
UNPIVOT  --2.列转行
(collection_info FOR month_info IN (month_0, month_1,month_2,month_3,month_4,month_5,month_6,month_7,month_8,month_9,month_10,month_11,month_12,month_13,month_14,month_15)) AS UnpivotTable
)
select new_totalreturnplanId,new_refreshdate,ownerid, CONVERT(VARCHAR(6),dateadd(MONTH,month_index,new_refreshdate), 112) yyyymm,
d.[1] as new_collectiondate,
d.[2] as new_collectionamount,
d.[3] as new_collectionamount_org
from cte c
CROSS APPLY--将拼接的collection_info拆分成多列
(
   SELECT  [1], [2],[3]
FROM   
(select value,(ROW_NUMBER() over (order by (select 1))) num from string_split(c.collection_info,'|')) SourceTable  
PIVOT  
(  
max(value)  
FOR num IN ([1],[2],[3])  
) AS PivotTable
)d

这里 CROSS APPLY 的是一个拼接字符串string_split而得来的表.

心得:

1.通常我们需要得到多个字段的值一般会想到通过join关联表返回,CROSS APPLY某种意义上和表连接的功能相同；

2.当我们不合适或者无法使用表连接查询一个表的多个字段时(如上,根本没有可以关联的表),你不应该重复使用子查询来查询这些字段,而是使用CROSS APPLY,将需要通过子查询得到的所有字段统一通过CROSS APPLY查出来,实现子查询后置,而不是在select后重复使用子查询每个字段查询一次.