仅获取按列分组的最新行

Question

我有大量的已发送电子邮件和状态码数据集。

ID Recipient           Date       Status
 1 [e-mail] 01/01/2010      1
 2 [e-mail] 02/01/2010      1
 3 [e-mail]    01/01/2010      1
 4 [e-mail]    02/01/2010      2
 5 [e-mail]    03/01/2010      1
 6 [e-mail]  01/01/2010      1
 7 [e-mail]  02/01/2010      2

在此示例中：

• 发送给 某人的 所有电子邮件的状态均为 1
• 发送给 他们 的中间电子邮件（按日期）的状态为 2 ，但最新的为 1
• 最近发送给 他人的 电子邮件的状态为 2

我需要检索的是发送给每个人的所有电子邮件的计数，以及 最新的 状态代码是什么。

第一部分非常简单：

SELECT Recipient, Count(*) EmailCount
FROM Messages
GROUP BY Recipient
ORDER BY Recipient

这给了我：

Recipient           EmailCount
[e-mail] 2
[e-mail]    3
[e-mail]  2

我如何也可以获得最新的状态代码？

最终结果应为：

Recipient           EmailCount LastStatus
[e-mail]          2          1
[e-mail]             3          1
[e-mail]           2          2

谢谢。

（html" target="_blank">服务器是Microsoft SQL Server 2008，正在通过.Net中的OleDbConnection运行查询）

Answer 1

这是“最大每组”查询的示例。我认为将其分为两个子查询然后将结果合并起来是最容易理解的。

第一个子查询就是您已经拥有的子查询。

第二个子查询使用窗口函数ROW_NUMBER对每个收件人的电子邮件进行编号，以最近的1开头，然后是2、3等等。

然后将第一个查询的结果与行号为1（即最新）的第二个查询的结果相结合。这样可以确保在有关系的情况下，每个收件人只能获得一行。

这是查询：

SELECT T1.Recipient, T1.EmailCount, T2.Status FROM
(
    SELECT Recipient, COUNT(*) AS EmailCount
    FROM Messages
    GROUP BY Recipient
) T1
JOIN
(
    SELECT
        Recipient,
        Status,
        ROW_NUMBER() OVER (PARTITION BY Recipient ORDER BY Date Desc) AS rn
    FROM Messages
) T2
ON T1.Recipient = T2.Recipient AND T2.rn = 1

得到以下结果：

Recipient            EmailCount  Status  
[e-mail]   2           2       
[e-mail]  2           1       
[e-mail]     3           1