下面是我需要优化的查询：
DECLARE @Power VARCHAR(10), @Button VARCHAR(10), @Casing VARCHAR(10), @Screen VARCHAR(10)
SELECT @Power = ActivityId FROM t_Activity WHERE ActivityName = 'PhonePowersUp?'
SELECT @Button = ActivityId FROM t_Activity WHERE ActivityName = 'T/S and Buttons functioning OK?'
SELECT @Casing = ActivityId FROM t_Activity WHERE ActivityName = 'Casing - no major defects?'
SELECT @Screen = ActivityId FROM t_Activity WHERE ActivityName = 'LCD works OK?'
SELECT
HQ.HandsetQuoteId [HandsetQuoteId], SS.Name [quote_status], HQ.QuoteDate [Quote Date], INS.DateInspected [DateInspected], PA.IMEI [IMEI_Quoted],
PA1.IMEI [IMEI_Inspected], INS.Grade [Grade], PB.PackageBoxName [PackageBoxName], CC.Name [ContactChannel], PhnBrd.Name [Brand], PM.ModelName [ModelQuoted],
PM1.ModelName [ModelInspected], U.FirstName [FirstName], U.Surname [Surname], U.Username [Username], UW.WarehouseId [WarehouseId], W.Name [Warehouse Name],
HQA.Value [Original Quote Value], HQ.QuoteValue [Quote Value], INS.InspectionValue [InspectionValue], HQ.AgreedValue [Agreed Value], CUS.FirstName [Store Name],
DATEDIFF(DAY, HQ.QuoteDate, GETDATE()) [Quote Age],
[ST_POWER] = CASE WHEN (CHARINDEX(','+ @Power +',', ','+PAR.Ok+',') > 0) THEN 'YES' WHEN (CHARINDEX(','+ @Power +',', ','+PAR.Fault+',') > 0) THEN 'NO' ELSE NULL END,
[ST_BUTTONS] = CASE WHEN (CHARINDEX(','+ @Button +',', ','+PAR.Ok+',') > 0) THEN 'YES' WHEN (CHARINDEX(','+ @Button +',', ','+PAR.Fault+',') > 0) THEN 'NO' ELSE NULL END,
[ST_CASING] = CASE WHEN (CHARINDEX(','+ @Casing +',', ','+PAR.Ok+',') > 0) THEN 'YES' WHEN (CHARINDEX(','+ @Casing +',', ','+PAR.Fault+',') > 0) THEN 'NO' ELSE NULL END,
[ST_Screen] = CASE WHEN (CHARINDEX(','+ @Screen +',', ','+PAR.Ok+',') > 0) THEN 'YES' WHEN (CHARINDEX(','+ @Screen +',', ','+PAR.Fault+',') > 0) THEN 'NO' ELSE NULL END,
st_deduct = PAR.PercentageDeduction, wt_deduct = MAX(APD.PercentageDeduction)
FROM t_Inspection AS INS
INNER JOIN t_HandsetQuote HQ ON HQ.HandsetQuoteId = INS.HandsetQuoteId
INNER JOIN t_QuoteHeader QH ON QH.QuoteHeaderId = HQ.QuoteHeaderId
INNER JOIN t_Customer CUS ON CUS.CustomerId = QH.CustomerId
INNER JOIN t_ContactChannel CC ON CC.ContactChannelId = CUS.ContactChannelId
INNER JOIN t_PackageBoxHandset PBH ON PBH.HandsetQuoteId = HQ.HandsetQuoteId
INNER JOIN t_PackageBox PB ON PB.PackageBoxId = PBH.PackageBoxId
INNER JOIN t_StockStatus SS ON SS.StockStatusId = HQ.StockStatusId
INNER JOIN t_PhoneAudit PA ON PA.PhoneAuditId = HQ.QuotePhoneAuditId
INNER JOIN t_PhoneModel PM ON PM.PhoneModelId = PA.PhoneModelId AND PA.PhoneAuditId = HQ.QuotePhoneAuditId
INNER JOIN t_PhoneBrand PhnBrd ON PM.PhoneBrandId = PhnBrd.PhoneBrandId
INNER JOIN t_PhoneAudit PA1 ON PA1.PhoneAuditId = HQ.InspectionPhoneAuditId
INNER JOIN t_PhoneModel PM1 ON PM1.PhoneModelId = PA1.PhoneModelId AND PA1.PhoneAuditId = HQ.InspectionPhoneAuditId
INNER JOIN t_PhoneBrand PhnBrd1 ON PM1.PhoneBrandId = PhnBrd1.PhoneBrandId
INNER JOIN t_User U ON INS.InspectorId = U.UserId
INNER JOIN t_UserWarehouse UW ON U.UserId = UW.UserId
INNER JOIN t_Warehouse W ON UW.WarehouseId = W.WarehouseId
LEFT JOIN t_HandsetQuoteAdditionalInfo HQA ON HQ.HandsetQuoteId = HQA.HandsetQuoteId AND HQA.KeyName = 'OriginalQuoteValue'
LEFT JOIN t_PhoneAuditRetail PAR ON PAR.HandsetQuoteId = HQ.HandsetQuoteId
LEFT JOIN t_HandsetQuoteActivity HQA1 ON HQA1.HandsetQuoteId = HQ.HandsetQuoteId AND HQA1.ActivityTestOK = 0 AND HQA1.ActivityStartTime = (
CASE
WHEN ((SELECT Count(1) FROM t_HandsetQuoteActivity WHERE HandsetQuoteId = HQA1.HandsetQuoteId AND ActivityStartTime <= QH.Cache_QuoteAcceptedDate AND ActivityId = HQA1.ActivityId) > 0)
THEN (SELECT Max(ActivityStartTime) FROM t_HandsetQuoteActivity WHERE HandsetQuoteId = HQA1.HandsetQuoteId AND ActivityStartTime <= QH.Cache_QuoteAcceptedDate AND ActivityId = HQA1.ActivityId GROUP BY HandsetQuoteId, ActivityId)
ELSE
(SELECT Min(ActivityStartTime) FROM t_HandsetQuoteActivity WHERE HandsetQuoteId = HQA1.HandsetQuoteId AND ActivityStartTime >= QH.Cache_QuoteAcceptedDate AND ActivityId = HQA1.ActivityId GROUP BY HandsetQuoteId, ActivityId)
END)
LEFT JOIN t_Activity_PercentageDeduction APD ON APD.ActivityId = HQA1.ActivityId AND APD.ContactChannelId = CUS.ContactChannelId AND APD.ContactChannelId = CC.ContactChannelId
WHERE Ins.DateInspected > GETDATE()-90
GROUP BY HQ.HandsetQuoteId, SS.Name, QH.CreatedDate, INS.DateInspected, PA.IMEI, PA1.IMEI, INS.Grade, PB.PackageBoxName, CC.Name, PhnBrd.Name, PM.ModelName, PM1.ModelName,
U.FirstName, U.Surname, U.Username, UW.WarehouseId, W.Name, HQA.Value, HQ.QuoteValue, INS.InspectionValue, HQ.AgreedValue, CUS.Firstname, HQ.QuoteDate, PAR.Ok, PAR.Fault,
PAR.PercentageDeduction
而且，在正确调试它之后，我开始知道当我从选择列表中删除wt_deduct = MAX(APD.PercentageDeduction)时，查询将在不到一分钟的时间内执行。
但是，但是我无法弄清楚，列APD.PercentageDeduction有什么问题，因为当我将其包含在选择列表中时，我的查询卡住了并且变得太慢并且需要15 minutes才能运行，并且将其从选择列表中排除会使查询运行30 seconds.
附加信息：表 -> t_Activity_PercentageDeduction 仅包含 400 条记录，列 PercentageDeduction 的数据类型Decimal。
如果您还需要其他信息，请告诉我。
如果我
没记错的话，你只是为了得到那个最大值而加入该表，通过添加这个最大值，你还必须做所有的分组。因此，查询实际上存在很大差异。
我建议使用共同相关的子查询来获取这段数据。
删除左联接
LEFT JOIN t_Activity_PercentageDeduction APD ON APD.ActivityId = HQA1.ActivityId AND APD.ContactChannelId = CUS.ContactChannelId AND APD.ContactChannelId = CC.ContactChannelId
并将 wt_deduct = 设置为新的协同相关子查询
wt_deduct = (select MAX(PercentageDeduction) from t_Activity_PercentageDeduction
where ActivityId = HQA1.ActivityId
AND ContactChannelId = CUS.ContactChannelId
AND ContactChannelId = CC.ContactChannelId)
您也可以删除所有不再需要的分组。