时间戳

首页 > 解决方案 > 仅选择具有特定状态的组中的最新版本

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

问题描述

在我正在进行的一个项目中，我有一张你可以在下面看到的表格。在前端，我只需要显示按 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

我创建了一个 DBFiddle 来玩： https ://www.db-fiddle.com/f/4UcjKhTvzzNQWL3Pfkfew4/1

注意这与SQL 仅选择列上具有最大值的行不同，因为那里的答案将选择所有已发布的修订，而不仅仅是最新的修订。

标签： mysqlsqlgreatest-n-per-group

解决方案

大概你在追求这样的事情......

DROP TABLE IF EXISTS entities;

CREATE TABLE `entities` 
( id SERIAL PRIMARY KEY
, entity_id INT NOT NULL
, revision INT NOT NULL DEFAULT '1'
, type enum('gym','trainer') 
, status enum('published','ready','draft','archived') NOT NULL DEFAULT 'draft'
, UNIQUE KEY entities_entity_id_revision_unique (entity_id,revision)
);


INSERT INTO entities
(id, entity_id, revision, type,status) VALUES
( 1,1, 1,'gym','published'),
( 2,1, 2,'gym','archived'),
( 3,2, 1,'gym','draft'),
( 4,2, 2,'gym','draft'),
( 5,2, 3,'gym','draft'),
( 6,2, 4,'gym','ready'),
( 7,2, 5,'gym','draft'),
( 8,2, 6,'gym','published'),
( 9,2, 7,'gym','published'),
(10,2, 8,'gym','ready'),
(11,2, 9,'gym','published'),
(13,3, 1,'gym','draft'),
(14,4, 1,'gym','draft'),
(15,4, 2,'gym','draft'),
(16,4, 3,'gym','published'),
(18,5, 1,'gym','draft'),
(19,5, 2,'gym','draft'),
(20,5, 3,'gym','draft'),
(21,5,10,'gym','published');
    
    
SELECT x.* 
  FROM entities x
  JOIN 
     ( SELECT entity_id
            , MAX(revision) revision
         FROM entities
        WHERE status = 'published'
        GROUP
           BY entity_id
     ) y
    ON y.entity_id = x.entity_id
   AND y.revision = x.revision;
   
   +----+-----------+----------+------+-----------+
   | id | entity_id | revision | type | status    |
   +----+-----------+----------+------+-----------+
   |  1 |         1 |        1 | gym  | published |
   | 11 |         2 |        9 | gym  | published |
   | 16 |         4 |        3 | gym  | published |
   | 21 |         5 |       10 | gym  | published |
   +----+-----------+----------+------+-----------+
   4 rows in set (0.00 sec)

推荐阅读