mysql - 获取每个类别的最新状态

Question

我遇到了这个有趣的问题。我有一个名为email_track跟踪每个类别的电子邮件状态的表格（邀请，通讯）

这就是我的表格数据的样子，

通过以下这些查询，我可以获得每个的最新记录to_email，

with `et2` as (
    select `et1`.`category`, `et1`.`to_email`, `et1`.`subject`, `et1`.`status`, ROW_NUMBER() OVER (partition by `to_email` order by `id` desc) as `rn`
    from `email_track` `et1`
)
select * from `et2` where `rn` = 1;

select `et1`.`category`, `et1`.`to_email`, `et1`.`subject`, `et1`.`status`, `et2`.`id`
from `email_track` `et1`
left join `email_track` `et2` on (`et1`.`to_email` = `et2`.`to_email` and `et1`.`id` < `et2`.`id`)
where `et2`.`id` is null;

我期望的是电子邮件[e-mail]我应该得到两条记录，一条用于类别邀请，另一条用于新闻通讯。现在，我们不会得到那个结果，因为我们按to_email

标签： mysqlsqlsql-order-bygreatest-n-per-groupwindow-functions

Answer 1

我应该得到两条记录，一条用于类别邀请，另一条用于时事通讯。现在，我们不会得到那个结果，因为我们按to_email.

添加category到partition by窗口函数的子句应该足以给你想要的结果：

with et2 as (
    select et1.category, et1.to_email, et1.subject, et1.status, 
        row_number() over(partition by to_email, category order by id desc) as rn
    from email_track et1
)
select * from et2 where rn = 1;