时间戳

首页 > 解决方案 > 使用 Group BY MAX 并与另一个 TABLE 连接的子查询优化查询

sql - 使用 Group BY MAX 并与另一个 TABLE 连接的子查询优化查询

问题描述

在此处输入图像描述我需要帮助来优化这个 SQL 查询，以便它运行得更快。

我想要做的是，从这些表中获取 DATA 的最新值：

表：报价
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 Mark someData
200 Lisa someData2
300 Brett someData3
400 Goku someData4

期望的结果：
LastUpdated Id(quoteId) QuoteNumber CustomerName
1001111111110- -1- -D2000- -Lisa
1002222222222- -2- -B2000- -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

标签： sqlsql-servergreatest-n-per-group

解决方案

我想你正在寻找这样的东西

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;

推荐阅读