JOIN與TOP1並用

有新的JOIN方式，但僅適用於2005以後的版本。

舊的寫法用SUB SELECT寫法會Timeout，新的只需17秒。

SELECT *
FROM FDRPSSDI AS D
OUTER APPLY (
SELECT TOP 1 NEWCARD, NEWPRI
FROM (
SELECT M2.STDATE, M2.ORDNO, D2.NEWCARD, D2.NEWPRI
FROM FDRCAMD1 AS D2
LEFT JOIN FDRCAMMI AS M2 ON M2.PROJNO = D2.PROJNO
LEFT JOIN FDRCAMD2 AS C2 ON M2.PROJNO = C2.PROJNO
WHERE 1 = 1
AND M2.SUITGONO = '1'
AND C2.CUNO = '0018'
AND M2.STDATE <= '20150601'
AND D2.GONO = D.GONO1
AND M2.YNSTATE = 'Y'
AND M2.NEVEREND <> 'Y'
UNION ALL
SELECT M2.STDATE, M2.ORDNO, D2.NEWCARD, D2.NEWPRI
FROM FDRCAMD1 AS D2
LEFT JOIN FDRCAMMI AS M2 ON M2.PROJNO = D2.PROJNO
WHERE 1 = 1
AND M2.SUITGONO = '2'
AND M2.STDATE <= '20150601'
AND D2.GONO = D.GONO
AND M2.YNSTATE = 'Y'
AND M2.NEVEREND <> 'Y'
UNION ALL
SELECT M2.STDATE, M2.ORDNO, D2.NEWCARD, D2.NEWPRI
FROM FDRCAMD1 AS D2
LEFT JOIN FDRCAMMI AS M2 ON M2.PROJNO = D2.PROJNO
LEFT JOIN FDRCAMD2 AS C2 ON M2.PROJNO = C2.PROJNO
WHERE 1 = 1
AND M2.SUITGONO = '1'
AND C2.CUNO = '0018'
AND M2.STDATE <= '20150601'
AND D2.GONO = D.GONO1
AND M2.YNSTATE = 'Y'
AND M2.NEVEREND = 'Y'
UNION ALL
SELECT M2.STDATE, M2.ORDNO, D2.NEWCARD, D2.NEWPRI
FROM FDRCAMD1 AS D2
LEFT JOIN FDRCAMMI AS M2 ON M2.PROJNO = D2.PROJNO
WHERE 1 = 1
AND M2.SUITGONO = '2'
AND M2.STDATE <= '20150601'
AND D2.GONO = D.GONO
AND M2.YNSTATE = 'Y'
AND M2.NEVEREND = 'Y'
) AS A
ORDER BY STDATE DESC, ORDNO) AS B
WHERE 1 = 1
AND D.CUNO = '0018'
AND D.PDATE = '20150601'
AND ISNULL(D.CARD2, '') = ''