仅选择具有特定状态的组中的最新版本

Question

在一个项目中，我正在工作，我有一个表，你可以看到下面。在前端，我只需要显示按entity_id分组发布的记录。例如，在下面的示例中，应该只显示id 1，11，16和19。我不知道怎么做这个查询。我用子查询等尝试了几种方法，但没有一种有效。我猜应该有办法找回这些数据。我错过了什么？

| id | revision   | entity_id   | status     
========================================
| 1  | 1          | 1           | published
| 2  | 2          | 1           | archived
| 3  | 1          | 2           | draft
| 4  | 2          | 2           | draft
| 5  | 3          | 2           | draft
| 6  | 4          | 2           | ready
| 7  | 5          | 2           | draft
| 8  | 6          | 2           | published
| 9  | 7          | 2           | published
| 10 | 8          | 2           | ready
| 11 | 9          | 2           | published
| 13 | 1          | 3           | draft
| 14 | 1          | 4           | draft
| 15 | 2          | 4           | draft
| 16 | 3          | 4           | published
| 18 | 1          | 5           | draft
| 19 | 2          | 5           | published
| 20 | 3          | 5           | draft
| 21 | 10         | 5           | archived 

Answer 1

您可以使用row_number()枚举每个实体的“已发布”行，然后将最新的行:

select t.*
from (select t.*, 
             row_number() over (partition by entity_id, status order by revision desc) as seqnum
      from t
      where status = 'published'
     ) t
 where seqnum = 1;

或者，可以在where子句中使用筛选:

select t.*
from t
where t.id = (select max(t2.id)
              from t t2
              where t2.entity_id = t.entity_id and
                    t2.status = 'published'
             );

这将为每个实体返回一行--“已发布”的id最大的行。