仅获取按列分组的最新行

小编典典

仅获取按列分组的最新行

sql

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

ID Recipient           Date       Status
 1 someone@example.com 01/01/2010      1
 2 someone@example.com 02/01/2010      1
 3 them@example.com    01/01/2010      1
 4 them@example.com    02/01/2010      2
 5 them@example.com    03/01/2010      1
 6 others@example.com  01/01/2010      1
 7 others@example.com  02/01/2010      2

在此示例中：

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

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

第一部分非常简单：

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

这给了我：

Recipient           EmailCount
someone@example.com 2
them@example.com    3
others@example.com  2

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

最终结果应为：

Recipient           EmailCount LastStatus
someone@example.com          2          1
them@example.com             3          1
others@example.com           2          2

谢谢。

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

阅读 166

2021-04-14

共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  
others@example.com   2           2       
someone@example.com  2           1       
them@example.com     3           1

2021-04-14