針對每個經銷商在各縣市的兩類客戶(ChannelID 1027、1028),依銷量排名取前 10% 進行電話調查。
SELECT DISTINCT
b.OrgID, b.CompanyID, b.AreaID, b.CustChannelID, b.CustID, b.SaleTotalQty,
GETDATE()
FROM KDS400TelSurveyCustAll_Temp AS tmp1
CROSS APPLY (
SELECT TOP 10 PERCENT *
FROM KDS400TelSurveyCustAll_Temp tmp2
WHERE tmp1.OrgID = tmp2.OrgID
AND tmp1.CompanyID = tmp2.CompanyID
AND tmp1.AreaID = tmp2.AreaID
AND tmp1.CustChannelID = tmp2.CustChannelID
AND tmp2.CustChannelID IN (1027, 1028)
ORDER BY tmp2.OrgID, tmp2.CompanyID, tmp2.AreaID, tmp2.AreaType, tmp2.CustChannelID
) AS b
ORDER BY 排序後,取前 10% 筆數(不足 1 筆按 1 筆計)KDS400TelSurveyCustAll_Temp 建立覆蓋索引:
CREATE INDEX IX_Survey_Group_Sale
ON KDS400TelSurveyCustAll_Temp (OrgID, CompanyID, AreaID, CustChannelID)
INCLUDE (CustID, SaleTotalQty, AreaType);
ROW_NUMBER() OVER (PARTITION BY ... ORDER BY SaleTotalQty DESC) 視窗函數配合 CTE,避免 CROSS APPLY 逐列掃描UPDATE STATISTICS 讓優化器正確估算 TOP PERCENT 筆數同樣模式適用於: