sql-server – 在哪里使用外部应用

主表

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时,我获得沟通的功效.

LEFT JOIN

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

外部申请

SELECT T1.ID,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

办理要领

1.假如我们想按照TOP n功效插手两个表

思量我们是否必要从Master中选择Id和Name,并从Details表中为每个Id选择最后两个日期.

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
LEFT JOIN
(
    SELECT TOP 2 ID,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,它也只会从具体信息表中带来最新的两个日期数据而不管Id.以是正确的办理方案是行使OUTER APPLY.

SELECT M.ID,D.QTY
FROM MASTER M
OUTER APPLY
(
    SELECT TOP 2 ID,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日期相团结,这将带来以下功效.

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.当我们必要行使函数的LEFT JOIN成果时.

当我们必要从主表和函数中获取功效时,OUTER APPLY可以用作LEFT JOIN的替代.

SELECT M.ID,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,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值

思量一下你有下表

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值.

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

发生以下功效.请留意,我们错过了身份证号码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,可以交流).

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

形成以下功效并保存其值为3的Id

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

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!