假设你有两个表。
主表
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
有很多情况下我们需要将INNER JOIN替换为CROSS APPLY。
1. 基于TOP n结果连接两个表
考虑如果我们需要从Master选择Id和Name，以及每个Id的最后两个日期从Details table中选择。
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
上述查询生成以下结果。
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在外部查询中连接这些记录，这是错误的。这应该返回Ids 1和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
并生成以下结果。
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 内部执行连接操作，即 WHERE M.ID=D.ID。
2. 当我们需要使用函数实现 INNER JOIN 功能时。
当我们需要从 Master 表和一个 function 获取结果时，可以使用 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
)
生成了以下结果：
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
CROSS APPLY 的其他优点
APPLY 可以用作 UNPIVOT 的替代方法。可以使用 CROSS APPLY 或 OUTER APPLY，这两者是可以互换的。
考虑您拥有以下表格（名为 MYTABLE）。
x
| Id | FROMDATE | TODATE |
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
以下是查询内容。
SELECT DISTINCT ID,DATES
FROM MYTABLE
CROSS APPLY(VALUES (FROMDATE),(TODATE))
COLUMNNAMES(DATES)
这可以帮您得到结果。
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
