使用带有Group BY MAX的子查询并与另一个表连接的优化查询
使用带有Group BY MAX的子查询并与另一个表连接的优化查询
提问于 2020-10-17 11:48:44
我需要帮助优化这个SQL查询，以便它运行得更快。
我要做的是，从这些表中获取数据的最新值：
表:引号
ID QuoteNumber LastUpdated(inticks) PolicyId
1 C1000 1000000000000 100
1 D2000 1001111111110 200
2 A1000 1000000000000 300
2 B2000 1002222222222 400
表:政策
ID CustomerName Blah1(虚拟列)
100马克someData
莉萨someData2 200
300布雷特someData3
400悟空someData4
预期结果：
LastUpdated Id(quoteId) QuoteNumber CustomerName
1001111111110- -1- -D 2000- -Lisa
1002222222222- -2- -B 2000- -Goku
Select DISTINCT subquery1.LastUpdated,
q2.Id,
q2.QuoteNumber,
p.CustomerName
FROM
(Select q.id,
Max(q.LastUpdated) from Quotes q
where q.LastUpdated > @someDateTimeParameter
and q.QuoteNumber is not null
and q.IsDiscarded = 0
GROUP BY q.id) as subquery1
LEFT JOIN Quotes q2
on q2.id = subquery1.id
and q2.LastUpdated = subquery1.LastUpdated
INNER JOIN Policies p
on p.id = q2.PolicyId
where p.blah1 = @someBlahParameter
ORDER BY subquery1.LastUpdated以下是实际的执行计划：https://www.brentozar.com/pastetheplan/?id=SkD3fPdwD
回答 1
Stack Overflow用户
回答已采纳
发布于 2020-10-17 12:06:36
我想你是在找这样的东西
with q_cte as (
select q.Id, q.QuoteNumber, q.LastUpdated,
row_number() over (partition by q.id order by q.LastUpdated desc) rn
from Quotes q
where q.LastUpdated>@someDateTimeParameter
and q.QuoteNumber is not null
and q.IsDiscarded=0)
select q.*, p.CustomerName
from q_cte q
join Policies p on q.PolicyId=p.id
where q.rn=1 /* Only the lastest date */
and p.blah1=someBlahParameter
order by q.LastUpdated;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/64402095
复制相关文章
相似问题
腾讯云开发者
