在哪里使用外层申请 [英] Where to use Outer Apply

问题描述

主表

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

使用LEFT JOIN和OUTER APPLY时得到相同的结果.

I am getting the same results when LEFT JOIN and OUTER APPLY is used.

LEFT JOIN

LEFT JOIN

SELECT T1.ID,T1.NAME,T2.PERIOD,T2.QTY 
FROM MASTER T1
LEFT JOIN DETAILS T2 ON T1.ID=T2.ID

OUTER APPLY

OUTER APPLY

SELECT T1.ID,T1.NAME,TAB.PERIOD,TAB.QTY 
FROM MASTER T1
OUTER APPLY
(
   SELECT ID,PERIOD,QTY 
   FROM DETAILS T2
   WHERE T1.ID=T2.ID
)TAB

我应该在哪里使用LEFT JOIN以及我应该在哪里使用OUTER APPLY

Where should I use LEFT JOIN AND where should I use OUTER APPLY

推荐答案

在以下情况下，应将LEFT JOIN替换为OUTER APPLY.

A LEFT JOIN should be replaced with OUTER APPLY in the following situations.

1.如果我们想根据TOP n结果联接两个表

1. If we want to join two tables based on TOP n results

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

Consider if we need to select Id and Name from Master and last two dates for each Id from Details table.

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
LEFT 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   |
|   2  |   B     |   NULL       |  NULL |
|   3  |   C     |   NULL       |  NULL |
x------x---------x--------------x-------x

这将带来错误的结果，即，即使我们使用Id进行连接，也将仅从Details表中获得最新的两个日期数据，而与Id无关.因此，正确的解决方案是使用OUTER APPLY.

This will bring wrong results ie, it will bring only latest two dates data from Details table irrespective of Id even though we join with Id. So the proper solution is using OUTER APPLY.

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

这是有效的方法:在LEFT JOIN中，仅在派生表D中执行查询后，TOP 2中的日期才会加入到MASTER中.在OUTER APPLY中，它使用OUTER APPLY内的连接WHERE M.ID=D.ID，以便将Master中的每个ID与TOP 2日期连接在一起，这将带来以下结果.

Here is the working : In LEFT JOIN , TOP 2 dates will be joined to the MASTER only after executing the query inside derived table D. In OUTER APPLY, it uses joining WHERE M.ID=D.ID inside the OUTER APPLY, so that each ID in Master will be joined with TOP 2 dates which will bring the following result.

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   |
|   3  |   C     |   NULL       |  NULL |
x------x---------x--------------x-------x

2.当我们需要使用functions的LEFT JOIN功能时.

2. When we need LEFT JOIN functionality using functions.

OUTER APPLY替换为LEFT JOIN.

SELECT M.ID,M.NAME,C.PERIOD,C.QTY
FROM MASTER M
OUTER 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   |
|   3  |   C     |   NULL       |  NULL |
x------x---------x--------------x-------x

3.取消旋转时保留NULL值

3. Retain NULL values when unpivoting

请考虑以下表格

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

使用UNPIVOT将FROMDATE和TODATE带到一列时，默认情况下它将消除NULL值.

When you use UNPIVOT to bring FROMDATE AND TODATE to one column, it will eliminate NULL values by default.

SELECT ID,DATES
FROM MYTABLE
UNPIVOT (DATES FOR COLS IN (FROMDATE,TODATE)) P

这将产生以下结果.请注意，我们错过了Id数字3

which generates the below result. Note that we have missed the record of Id number 3

  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 |
  x------x-------------x

在这种情况下，可以使用APPLY(可以互换的CROSS APPLY或OUTER APPLY.)

In such cases an APPLY can be used(either CROSS APPLY or OUTER APPLY, which is interchangeable).

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

形成以下结果，并保留Id，其值为3

which forms the following result and retains Id where its value is 3

  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

这篇关于在哪里使用外层申请的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！