MYSQL查询查找具有指定列分组的所有重复记录

Question

我有一个表，其中包含为电子邮件事件发送的所有电子邮件:

ID | campaign_id | date_sent | date_delivered | email | status

Status 包含交付信息，DELIVERED 或 UNDELIVERED。我想查找最近五个事件中状态为 UNDELIVERED 的所有电子邮件地址。

例如，email@address.com 以前是可以的并且收到了所有的事件，但是现在电子邮件地址不存在并且最后 5 个事件状态是 UNDELIVERED .

目前我在做:

SELECT *, count(id) as occ FROM message_details WHERE status='FAILED' GROUP BY email

但这显示的是每个地址的未送达总数，而不是最后 5 个。

Answer 1

这可能读起来有点棘手。基本上，它试图做的是获取每封电子邮件的前 5 个 message_details，然后选择失败次数等于 5 的邮件。

虽然没有时间测试这段代码。

SELECT *, count(thirdTable.id) as failTimes FROM
(SELECT rs.id, rs.Status, rs.email
    FROM (
        SELECT *, Rank() 
          over (Partition BY Section
                ORDER BY RankCriteria DESC ) AS Rank
        FROM message_details 
        ) rs WHERE Rank <= 5 ) as thirdTable
Where thirdTable.status = 'FAILED' GROUP BY thirdTable.email HAVING failTimes = 5