交叉应用有时可以让您做一些内部连接无法做到的事情。

示例(语法错误):

select F.* from sys.objects O  
inner join dbo.myTableFun(O.name) F   
on F.schema_id= O.schema_id

这是一个语法错误，因为当与内部连接一起使用时，表函数只能接受变量或常量作为参数。(也就是说，表函数参数不能依赖于另一个表的列。)

然而:

select F.* from sys.objects O  
cross apply ( select * from dbo.myTableFun(O.name) ) F  
where F.schema_id= O.schema_id

这是合法的。

编辑: 或者，更短的语法:(通过ErikE)

select F.* from sys.objects O  
cross apply dbo.myTableFun(O.name) F
where F.schema_id= O.schema_id

编辑:

注意: Informix 12.10 xC2+有横向派生表，Postgresql(9.3+)有横向子查询，可以达到类似的效果。

假设您有两张桌子。

掌握表

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

好吧，我不确定这是否有资格作为使用交叉应用与内部连接的原因，但这个查询在论坛帖子中使用交叉应用为我回答了，所以我不确定是否有一个等效的方法使用内部连接:

Create PROCEDURE [dbo].[Message_FindHighestMatches]

-- Declare the Topical Neighborhood
@TopicalNeighborhood nchar(255)

作为 开始

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

Create table  #temp
(
    MessageID         int,
    Subjects          nchar(255),
    SubjectsCount    int
)

Insert into #temp Select MessageID, Subjects, SubjectsCount From Message

Select Top 20 MessageID, Subjects, SubjectsCount,
    (t.cnt * 100)/t3.inputvalues as MatchPercentage

From #temp 

cross apply (select count(*) as cnt from dbo.Split(Subjects,',') as t1
             join dbo.Split(@TopicalNeighborhood,',') as t2
             on t1.value = t2.value) as t
cross apply (select count(*) as inputValues from dbo.Split(@TopicalNeighborhood,',')) as t3

Order By MatchPercentage desc

drop table #temp

END

下面是一个简短的教程，可以保存在.sql文件中，并在SSMS中执行，这是我为自己编写的，可以快速刷新我对CROSS APPLY如何工作以及何时使用它的记忆:

-- Here's the key to understanding CROSS APPLY: despite the totally different name, think of it as being like an advanced 'basic join'.
-- A 'basic join' gives the Cartesian product of the rows in the tables on both sides of the join: all rows on the left joined with all rows on the right.
-- The formal name of this join in SQL is a CROSS JOIN.  You now start to understand why they named the operator CROSS APPLY.

-- Given the following (very) simple tables and data:
CREATE TABLE #TempStrings ([SomeString] [nvarchar](10) NOT NULL);
CREATE TABLE #TempNumbers ([SomeNumber] [int] NOT NULL);
CREATE TABLE #TempNumbers2 ([SomeNumber] [int] NOT NULL);
INSERT INTO #TempStrings VALUES ('111'); INSERT INTO #TempStrings VALUES ('222');
INSERT INTO #TempNumbers VALUES (111); INSERT INTO #TempNumbers VALUES (222);
INSERT INTO #TempNumbers2 VALUES (111); INSERT INTO #TempNumbers2 VALUES (222); INSERT INTO #TempNumbers2 VALUES (222);

-- Basic join is like CROSS APPLY; 2 rows on each side gives us an output of 4 rows, but 2 rows on the left and 0 on the right gives us an output of 0 rows:
SELECT
    st.SomeString, nbr.SomeNumber
FROM -- Basic join ('CROSS JOIN')
    #TempStrings st, #TempNumbers nbr
    -- Note: this also works:
    --#TempStrings st CROSS JOIN #TempNumbers nbr

-- Basic join can be used to achieve the functionality of INNER JOIN by first generating all row combinations and then whittling them down with a WHERE clause:
SELECT
    st.SomeString, nbr.SomeNumber
FROM -- Basic join ('CROSS JOIN')
    #TempStrings st, #TempNumbers nbr
WHERE
    st.SomeString = nbr.SomeNumber

-- However, for increased readability, the SQL standard introduced the INNER JOIN ... ON syntax for increased clarity; it brings the columns that two tables are
-- being joined on next to the JOIN clause, rather than having them later on in the WHERE clause.  When multiple tables are being joined together, this makes it
-- much easier to read which columns are being joined on which tables; but make no mistake, the following syntax is *semantically identical* to the above syntax:
SELECT
    st.SomeString, nbr.SomeNumber
FROM -- Inner join
    #TempStrings st INNER JOIN #TempNumbers nbr ON st.SomeString = nbr.SomeNumber

-- Because CROSS APPLY is generally used with a subquery, the subquery's WHERE clause will appear next to the join clause (CROSS APPLY), much like the aforementioned
-- 'ON' keyword appears next to the INNER JOIN clause.  In this sense, then, CROSS APPLY combined with a subquery that has a WHERE clause is like an INNER JOIN with
-- an ON keyword, but more powerful because it can be used with subqueries (or table-valued functions, where said WHERE clause can be hidden inside the function).
SELECT
    st.SomeString, nbr.SomeNumber
FROM
    #TempStrings st CROSS APPLY (SELECT * FROM #TempNumbers tempNbr WHERE st.SomeString = tempNbr.SomeNumber) nbr

-- CROSS APPLY joins in the same way as a CROSS JOIN, but what is joined can be a subquery or table-valued function.  You'll still get 0 rows of output if
-- there are 0 rows on either side, and in this sense it's like an INNER JOIN:
SELECT
    st.SomeString, nbr.SomeNumber
FROM
    #TempStrings st CROSS APPLY (SELECT * FROM #TempNumbers tempNbr WHERE 1 = 2) nbr

-- OUTER APPLY is like CROSS APPLY, except that if one side of the join has 0 rows, you'll get the values of the side that has rows, with NULL values for
-- the other side's columns.  In this sense it's like a FULL OUTER JOIN:
SELECT
    st.SomeString, nbr.SomeNumber
FROM
    #TempStrings st OUTER APPLY (SELECT * FROM #TempNumbers tempNbr WHERE 1 = 2) nbr

-- One thing CROSS APPLY makes it easy to do is to use a subquery where you would usually have to use GROUP BY with aggregate functions in the SELECT list.
-- In the following example, we can get an aggregate of string values from a second table based on matching one of its columns with a value from the first
-- table - something that would have had to be done in the ON clause of the LEFT JOIN - but because we're now using a subquery thanks to CROSS APPLY, we
-- don't need to worry about GROUP BY in the main query and so we don't have to put all the SELECT values inside an aggregate function like MIN().
SELECT
    st.SomeString, nbr.SomeNumbers
FROM
    #TempStrings st CROSS APPLY (SELECT SomeNumbers = STRING_AGG(tempNbr.SomeNumber, ', ') FROM #TempNumbers2 tempNbr WHERE st.SomeString = tempNbr.SomeNumber) nbr
-- ^ First the subquery is whittled down with the WHERE clause, then the aggregate function is applied with no GROUP BY clause; this means all rows are
--   grouped into one, and the aggregate function aggregates them all, in this case building a comma-delimited string containing their values.

DROP TABLE #TempStrings;
DROP TABLE #TempNumbers;
DROP TABLE #TempNumbers2;

在我看来，CROSS APPLY可以在复杂/嵌套查询中处理计算字段时填补一定的空白，并使它们更简单，更易于阅读。

简单的例子:你有一个DoB，你想要显示多个与年龄相关的字段，这些字段也依赖于其他数据源(比如就业)，比如Age、AgeGroup、AgeAtHiring、MinimumRetirementDate等，以便在最终用户应用程序中使用(例如Excel数据透视表)。

选择是有限的，很少是优雅的:

JOIN subqueries cannot introduce new values in the dataset based on data in the parent query (it must stand on its own). UDFs are neat, but slow as they tend to prevent parallel operations. And being a separate entity can be a good (less code) or a bad (where is the code) thing. Junction tables. Sometimes they can work, but soon enough you're joining subqueries with tons of UNIONs. Big mess. Create yet another single-purpose view, assuming your calculations don't require data obtained mid-way through your main query. Intermediary tables. Yes... that usually works, and often a good option as they can be indexed and fast, but performance can also drop due to to UPDATE statements not being parallel and not allowing to cascade formulas (reuse results) to update several fields within the same statement. And sometimes you'd just prefer to do things in one pass. Nesting queries. Yes at any point you can put parenthesis on your entire query and use it as a subquery upon which you can manipulate source data and calculated fields alike. But you can only do this so much before it gets ugly. Very ugly. Repeating code. What is the greatest value of 3 long (CASE...ELSE...END) statements? That's gonna be readable! Tell your clients to calculate the damn things themselves.

我错过什么了吗?可能吧，请随意评论。但是，嘿，CROSS APPLY在这种情况下就像天赐之物:您只需添加一个简单的CROSS APPLY(选择tbl。value + 1 as someFormula)作为crossTbl和voilà!您的新字段现在已经可以使用了，就像它一直在源数据中一样。

通过CROSS APPLY引入的值可以…

用于创建一个或多个计算字段，而不会增加性能、复杂性或可读性问题 像join一样，几个后续的CROSS APPLY语句可以引用它们自己:someFormula + 1 as someMoreFormula) as crossTbl2 您可以在后续的JOIN条件中使用CROSS APPLY引入的值 另外，还有表值函数方面

该死，没有什么是他们做不到的!

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

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

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