在不使用Outer Apply的情况下重构SQL代码

Question

我下面有SQL代码，我需要使用LEFT JOIN重构它，但不使用OUTER APPLY。你能帮我一下吗？

SELECT cur.downtimedataid,
cur.serverid,
cur.downtimestart,
cur.downtimefinish,
cur.downtimeinfo1,
cur.downtimeinfo2,
cur.newproductionorderid,
cur.plantid,
cur.workcenterid,
cur.dm_plantid,
PR.previousorderid
FROM   bcs cur 
/*Function to find Previous Order*/
OUTER APPLY (SELECT TOP 1 prev.newproductionorderid AS PreviousOrderID,
prev.PlantID
FROM   bcs prev
WHERE  prev.serverid = cur.serverid
AND prev.workcenterid = cur.workcenterid
AND prev.downtimestart < cur.downtimefinish
ORDER  BY prev.downtimestart DESC) PR

Answer 1

您可以使用相关子查询：

SELECT cur.downtimedataid,
cur.serverid,
cur.downtimestart,
cur.downtimefinish,
cur.downtimeinfo1,
cur.downtimeinfo2,
cur.newproductionorderid,
cur.plantid,
cur.workcenterid,
cur.dm_plantid,
PR.previousorderid,
(SELECT TOP 1 prev.newproductionorderid AS PreviousOrderID,
prev.PlantID
FROM   bcs prev
WHERE  prev.serverid = cur.serverid
AND prev.workcenterid = cur.workcenterid
AND prev.downtimestart < cur.downtimefinish
ORDER  BY prev.downtimestart DESC
) as previousorderid
FROM   bcs cur