主表
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
解决方法
在以下情况下,应使用OUTER APPLY替换LEFT JOIN.
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
当我们需要从主表和函数中获取结果时,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
