获取每个类别的最新状态

本文介绍了获取每个类别的最新状态的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我遇到了一个有趣的问题.我有一个名为email_track的表，用于跟踪每个类别(如邀请，新闻稿)的电子邮件状态

I came across this interesting problem. I have a table named email_track to track email status for each category say (invitation, newsletter)

这是我的表格数据的外观，

This is how my table data looks,

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

With these following queries I'm able to get most recent record for each 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;

我期望的是电子邮件john@example，我应该获得两条记录，一条用于类别邀请，另一条用于新闻通讯.现在，由于我们按to_email进行分区，因此不会得到该结果.

What I'm expecting is for email john@example I should get two records one for category invitation and the other for the newsletter. Now, we won't get that result since we partition by to_email

推荐答案

我应该获得两条记录，一条用于类别邀请，另一条用于新闻通讯.现在，由于我们按to_email进行分区，因此不会得到该结果.

I should get two records one for category invitation and the other for the newsletter. Now, we won't get that result since we partition by to_email.

在窗口函数的partition by子句中添加category应该足以为您提供所需的结果:

Adding the category to the partition by clause of the window function should be enough to give your the result that you want:

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;