Code
2023-04-12 06:00:00

什么时候我应该在内部连接上使用交叉应用?

使用CROSS APPLY的主要目的是什么?

我已经读到(模糊地，通过互联网上的帖子)，如果您正在分区，那么在选择大型数据集时，交叉应用可以更有效。(想到寻呼)

我还知道CROSS APPLY不需要UDF作为右表。

在大多数INNER JOIN查询(一对多关系)中，我可以使用CROSS APPLY重写它们，但它们总是给我相同的执行计划。

谁能给我一个很好的例子，CROSS APPLY在那些INNER JOIN也能工作的情况下发挥作用?

编辑:

这里有一个简单的例子，其中执行计划完全相同。(告诉我一个它们的不同之处，交叉应用在哪里更快/更有效)

create table Company (
    companyId int identity(1,1)
,   companyName varchar(100)
,   zipcode varchar(10) 
,   constraint PK_Company primary key (companyId)
)
GO

create table Person (
    personId int identity(1,1)
,   personName varchar(100)
,   companyId int
,   constraint FK_Person_CompanyId foreign key (companyId) references dbo.Company(companyId)
,   constraint PK_Person primary key (personId)
)
GO

insert Company
select 'ABC Company', '19808' union
select 'XYZ Company', '08534' union
select '123 Company', '10016'


insert Person
select 'Alan', 1 union
select 'Bobby', 1 union
select 'Chris', 1 union
select 'Xavier', 2 union
select 'Yoshi', 2 union
select 'Zambrano', 2 union
select 'Player 1', 3 union
select 'Player 2', 3 union
select 'Player 3', 3 


/* using CROSS APPLY */
select *
from Person p
cross apply (
    select *
    from Company c
    where p.companyid = c.companyId
) Czip

/* the equivalent query using INNER JOIN */
select *
from Person p
inner join Company c on p.companyid = c.companyId

当前回答

假设您有两张桌子。

掌握表

x------x--------------------x
| Id   |        Name        |
x------x--------------------x
|  1   |          A         |
|  2   |          B         |
|  3   |          C         |
x------x--------------------x

详细信息表

x------x--------------------x-------x
| Id   |      PERIOD        |   QTY |
x------x--------------------x-------x
|  1   |   2014-01-13       |   10  |
|  1   |   2014-01-11       |   15  |
|  1   |   2014-01-12       |   20  |
|  2   |   2014-01-06       |   30  |
|  2   |   2014-01-08       |   40  |
x------x--------------------x-------x

在很多情况下，我们需要用CROSS APPLY替换INNER JOIN。

1. 根据TOP n结果连接两个表

考虑是否需要从Master中选择Id和Name，并从Details表中为每个Id选择最后两个日期。

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
INNER JOIN
(
    SELECT TOP 2 ID, PERIOD,QTY 
    FROM DETAILS D      
    ORDER BY CAST(PERIOD AS DATE)DESC
)D
ON M.ID=D.ID

SQL小提琴

上述查询生成以下结果。

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
x------x---------x--------------x-------x

看，它用最后两个日期的Id生成了最近两个日期的结果，然后只在Id上的外部查询中联接这些记录，这是错误的。这应该返回id 1和id 2，但它只返回1，因为1有最后两个日期。为了实现这一点，我们需要使用CROSS APPLY。

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
CROSS APPLY
(
    SELECT TOP 2 ID, PERIOD,QTY 
    FROM DETAILS D  
    WHERE M.ID=D.ID
    ORDER BY CAST(PERIOD AS DATE)DESC
)D

SQL小提琴

形成了下面的结果。

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-08   |  40   |
|   2  |   B     | 2014-01-06   |  30   |
x------x---------x--------------x-------x

下面是它的工作原理。CROSS APPLY内部的查询可以引用外部表，而INNER JOIN不能这样做(它会抛出编译错误)。当找到最后两个日期时，在CROSS APPLY中进行连接，即m.d id = d.d id。

2. 当我们需要INNER JOIN功能时使用函数。

当我们需要从主表和函数中获取结果时，可以使用CROSS APPLY代替INNER JOIN。

SELECT M.ID,M.NAME,C.PERIOD,C.QTY
FROM MASTER M
CROSS APPLY dbo.FnGetQty(M.ID) C

这就是函数

CREATE FUNCTION FnGetQty 
(   
    @Id INT 
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT ID,PERIOD,QTY 
    FROM DETAILS
    WHERE ID=@Id
)

SQL小提琴

这产生了以下结果

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-11   |  15   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-06   |  30   |
|   2  |   B     | 2014-01-08   |  40   |
x------x---------x--------------x-------x

交叉应用的额外优势

APPLY可以用来代替UNPIVOT。这里可以使用CROSS APPLY或OUTER APPLY，它们是可互换的。

假设您有下面的表(名为MYTABLE)。

x------x-------------x--------------x
|  Id  |   FROMDATE  |   TODATE     |
x------x-------------x--------------x
|   1  |  2014-01-11 | 2014-01-13   | 
|   1  |  2014-02-23 | 2014-02-27   | 
|   2  |  2014-05-06 | 2014-05-30   | 
|   3  |     NULL    |    NULL      |
x------x-------------x--------------x

查询如下。

SELECT DISTINCT ID,DATES
FROM MYTABLE 
CROSS APPLY(VALUES (FROMDATE),(TODATE))
COLUMNNAMES(DATES)

SQL小提琴

结果是什么

  x------x-------------x
  | Id   |    DATES    |
  x------x-------------x
  |  1   |  2014-01-11 |
  |  1   |  2014-01-13 |
  |  1   |  2014-02-23 |
  |  1   |  2014-02-27 |
  |  2   |  2014-05-06 |
  |  2   |  2014-05-30 | 
  |  3   |    NULL     | 
  x------x-------------x
2015-02-26 02:12:02

其他回答

这是一篇解释这一切的文章，以及它们在join中的性能差异和用法。

SQL Server交叉应用和外部应用连接

正如本文所建议的，对于正常的连接操作(INNER和CROSS)，它们之间没有性能差异。

当你必须执行这样的查询时，使用差异就出现了:

CREATE FUNCTION dbo.fn_GetAllEmployeeOfADepartment(@DeptID AS INT)  
RETURNS TABLE 
AS 
RETURN 
   ( 
   SELECT * FROM Employee E 
   WHERE E.DepartmentID = @DeptID 
   ) 
GO 

SELECT * FROM Department D 
CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)

也就是说，当你必须和函数联系起来的时候。这不能使用INNER JOIN来完成，这会给您一个错误“不能绑定多部分标识符”D.DepartmentID。在这里，读取每一行时将值传递给函数。听起来很酷。：）

2016-03-21 04:44:34

当你需要子查询的列时，交叉应用可以用来替换子查询

子查询

select * from person p where
p.companyId in(select c.companyId from company c where c.companyname like '%yyy%')

在这里，我将无法选择公司表的列 使用交叉应用

select P.*,T.CompanyName
from Person p
cross apply (
    select *
    from Company C
    where p.companyid = c.companyId and c.CompanyName like '%yyy%'
) T
2014-12-11 09:51:12

假设您有两张桌子。

掌握表

x------x--------------------x
| Id   |        Name        |
x------x--------------------x
|  1   |          A         |
|  2   |          B         |
|  3   |          C         |
x------x--------------------x

详细信息表

x------x--------------------x-------x
| Id   |      PERIOD        |   QTY |
x------x--------------------x-------x
|  1   |   2014-01-13       |   10  |
|  1   |   2014-01-11       |   15  |
|  1   |   2014-01-12       |   20  |
|  2   |   2014-01-06       |   30  |
|  2   |   2014-01-08       |   40  |
x------x--------------------x-------x

在很多情况下，我们需要用CROSS APPLY替换INNER JOIN。

1. 根据TOP n结果连接两个表

考虑是否需要从Master中选择Id和Name，并从Details表中为每个Id选择最后两个日期。

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
INNER JOIN
(
    SELECT TOP 2 ID, PERIOD,QTY 
    FROM DETAILS D      
    ORDER BY CAST(PERIOD AS DATE)DESC
)D
ON M.ID=D.ID

SQL小提琴

上述查询生成以下结果。

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
x------x---------x--------------x-------x

看，它用最后两个日期的Id生成了最近两个日期的结果，然后只在Id上的外部查询中联接这些记录，这是错误的。这应该返回id 1和id 2，但它只返回1，因为1有最后两个日期。为了实现这一点，我们需要使用CROSS APPLY。

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
CROSS APPLY
(
    SELECT TOP 2 ID, PERIOD,QTY 
    FROM DETAILS D  
    WHERE M.ID=D.ID
    ORDER BY CAST(PERIOD AS DATE)DESC
)D

SQL小提琴

形成了下面的结果。

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-08   |  40   |
|   2  |   B     | 2014-01-06   |  30   |
x------x---------x--------------x-------x

下面是它的工作原理。CROSS APPLY内部的查询可以引用外部表，而INNER JOIN不能这样做(它会抛出编译错误)。当找到最后两个日期时，在CROSS APPLY中进行连接，即m.d id = d.d id。

2. 当我们需要INNER JOIN功能时使用函数。

当我们需要从主表和函数中获取结果时，可以使用CROSS APPLY代替INNER JOIN。

SELECT M.ID,M.NAME,C.PERIOD,C.QTY
FROM MASTER M
CROSS APPLY dbo.FnGetQty(M.ID) C

这就是函数

CREATE FUNCTION FnGetQty 
(   
    @Id INT 
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT ID,PERIOD,QTY 
    FROM DETAILS
    WHERE ID=@Id
)

SQL小提琴

这产生了以下结果

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-11   |  15   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-06   |  30   |
|   2  |   B     | 2014-01-08   |  40   |
x------x---------x--------------x-------x

交叉应用的额外优势

APPLY可以用来代替UNPIVOT。这里可以使用CROSS APPLY或OUTER APPLY，它们是可互换的。

假设您有下面的表(名为MYTABLE)。

x------x-------------x--------------x
|  Id  |   FROMDATE  |   TODATE     |
x------x-------------x--------------x
|   1  |  2014-01-11 | 2014-01-13   | 
|   1  |  2014-02-23 | 2014-02-27   | 
|   2  |  2014-05-06 | 2014-05-30   | 
|   3  |     NULL    |    NULL      |
x------x-------------x--------------x

查询如下。

SELECT DISTINCT ID,DATES
FROM MYTABLE 
CROSS APPLY(VALUES (FROMDATE),(TODATE))
COLUMNNAMES(DATES)

SQL小提琴

结果是什么

  x------x-------------x
  | Id   |    DATES    |
  x------x-------------x
  |  1   |  2014-01-11 |
  |  1   |  2014-01-13 |
  |  1   |  2014-02-23 |
  |  1   |  2014-02-27 |
  |  2   |  2014-05-06 |
  |  2   |  2014-05-30 | 
  |  3   |    NULL     | 
  x------x-------------x
2015-02-26 02:12:02

交叉应用也适用于XML字段。如果您希望结合其他字段选择节点值。

例如，如果您有一个包含一些xml的表

根> < < subnode1 > <some_node value="1" /> <some_node value="2" /> <some_node value="3" /> <some_node value="4" /> < / subnode1 > 根> < /

使用查询

SELECT
       id as [xt_id]
      ,xmlfield.value('(/root/@attribute)[1]', 'varchar(50)') root_attribute_value
  ,node_attribute_value = [some_node].value('@value', 'int')
  ,lt.lt_name   
FROM dbo.table_with_xml xt
CROSS APPLY xmlfield.nodes('/root/subnode1/some_node') as g ([some_node])
LEFT OUTER JOIN dbo.lookup_table lt
ON [some_node].value('@value', 'int') = lt.lt_id

将返回一个结果

xt_id root_attribute_value node_attribute_value lt_name
----------------------------------------------------------------------
1     test1            1                    Benefits
1     test1            4                    FINRPTCOMPANY
2013-02-01 18:52:17

这可能是一个老问题，但我仍然喜欢CROSS APPLY的强大功能，它简化了逻辑的重用，并为结果提供了一种“链接”机制。

我在下面提供了一个SQL Fiddle，它展示了一个简单的示例，说明如何使用CROSS APPLY对数据集执行复杂的逻辑操作，而不会让事情变得一团糟。从这里不难推断出更复杂的计算。

http://sqlfiddle.com/ !3/23862/2

2015-10-06 01:41:57

推荐文章

aliyun

最新文章

标签

2024 code 京ICP备15047053号-1