SELECT *
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY task.FormId ORDER BY task.StartDate DESC) AS rownum, dbo.TRebatesForm.RebatesFormID, dbo.TRebatesForm.DealerCode, dbo.TRebatesForm.DealerName,
(SELECT TOP (1) EndDate
FROM dbo.TTask
WHERE (FormId = dbo.TRebatesForm.RebatesFormID) AND (IsFinished = 1) AND (StepState <> 777) AN
(StepState <> 888)
ORDER BY EndDate DESC) AS PreEndDate,
(SELECT TOP (1) StepId
FROM dbo.TTrack
WHERE (FormId = dbo.TRebatesForm.RebatesFormID) AND (StepState <> 1) AND (StepState <> 777) AND
(StepState <> 888)
ORDER BY EndDate DESC) AS PreStepId,
(SELECT TOP (1) StepState
FROM dbo.TTrack
WHERE (FormId = dbo.TRebatesForm.RebatesFormID) AND (StepState <> 1) AND (StepState <> 777) AND
(StepState <> 888)
ORDER BY EndDate DESC) AS PreStepState,
(SELECT TOP (1) RoleId
FROM dbo.TTrack
WHERE (FormId = dbo.TRebatesForm.RebatesFormID) AND (StepState <> 1) AND (StepState <> 777) AND
(StepState <> 888)
ORDER BY EndDate DESC) AS PreRoleId,
(SELECT TOP (1) CompleteUser
FROM dbo.TTask
WHERE (FormId = dbo.TRebatesForm.RebatesFormID) AND (IsFinished = 1) AND (StepState <> 777) AND
(StepState <> 888)
ORDER BY EndDate DESC) AS PreCompleteUser
FROM dbo.TRebatesForm
INNER JOIN dbo.TRebatesType on dbo.TRebatesForm.RebatesType = dbo.TRebatesType.TypeValue
LEFT JOIN dbo.TRebatesCustomer on dbo.TRebatesForm.RebatesCustomerID = dbo.TRebatesCustomer.RebatesCustomerID
LEFT JOIN tdealer d on d.DealerCode=dbo.TRebatesForm.DealerCode
INNER JOIN TTask task ON dbo.TRebatesForm.RebatesFormID = task.FormId
LEFT JOIN TTrack AS track ON task.TaskId = track.TaskId
LEFT JOIN [TVWTaskRebate] as TW on TW.FormId=TRebatesForm.RebatesFormID) T
WHERE rownum = 1
uj5u.com熱心網友回復：
--1. 把主表放在臨時表
SELECT *
INTO #tmp
FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY task.FormId ORDER BY task.StartDate DESC) AS rownum,*
FROM dbo.TRebatesForm
) T
WHERE rownum = 1
--2. 創建連接欄位索引，做一個，其它的你自己加
CREATE INDEX ix_#tmp_RebatesFormID ON #tmp (RebatesFormID);
--3. 查詢
SELECT dbo.TRebatesForm.RebatesFormID,
dbo.TRebatesForm.DealerCode,
dbo.TRebatesForm.DealerName,
tt.EndDate AS PreEndDate,
tt.StepId AS PreStepId,
tt.StepState AS PreStepState,
tt.RoleId AS PreRoleId,
tt.CompleteUser AS PreCompleteUser
FROM #tmp AS TRebatesForm
INNER JOIN dbo.TRebatesType
ON TRebatesForm.RebatesType = dbo.TRebatesType.TypeValue
LEFT JOIN dbo.TRebatesCustomer
ON TRebatesForm.RebatesCustomerID = dbo.TRebatesCustomer.RebatesCustomerID
LEFT JOIN tdealer d
ON d.DealerCode = TRebatesForm.DealerCode
INNER JOIN TTask task
ON TRebatesForm.RebatesFormID = task.FormId
LEFT JOIN TTrack AS track
ON task.TaskId = track.TaskId
LEFT JOIN [TVWTaskRebate] AS TW
ON TW.FormId = TRebatesForm.RebatesFormID
OUTER APPLY (
SELECT TOP(1) *
FROM dbo.TTask
WHERE (FormId = dbo.TRebatesForm.RebatesFormID)
AND (IsFinished = 1)
AND (StepState <> 777)
AND (StepState <> 888)
ORDER BY
EndDate DESC
) AS tt
--4. 洗掉臨時表
DROP TABLE #tmp
uj5u.com熱心網友回復：
OUTER APPLY 這樣效率不高，沒有原來查詢的快，根據特定條件查找是很慢uj5u.com熱心網友回復：
outer apply 不快， 但只查了一次， 你那樣要查很多次。另外， 快不快， 要看實際的效果和執行計劃， 你先試著做了再說吧。
原來多少秒， 修改后（記得加上索引）多少秒， 你貼出實際的測驗資料。
uj5u.com熱心網友回復：
當where篩選完資料如果資料量很小的話，直接top值大概5秒出結果，而 OUTER APPLY當我資料量很小時做關聯就很費時，因為TTask、和TTrack表資料量很大，如果要是查出資料量多的話是你的這中快，所以很尷尬轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/47996.html
標籤：疑難問題
上一篇：28轉行，學運維還是測驗
下一篇：求助