MySQL语句选择特定列的最新条目

问题描述：

我正在使用MySQL，并且该表是使用以下架构创建的：

I am using MySQL and the table was created with this schema:

CREATE TABLE `example` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `version` INT UNSIGNED NOT NULL,
  `text` VARCHAR(45) NOT NULL,
  `class_id` INT NOT NULL,
  `tyoe_id` INT NULL,
  PRIMARY KEY (`id`));

该表具有以下条目，如链接所示（不允许嵌入）。

The table has the following entries as seen in the link (not allowed to embed).

我想要特定的class_id（例如10）以获得所有具有最高版本的不同type_id。可能存在或不存在5个type_id 1,2,3,4,5，但是如果在特定的类中存在type_id，我们需要最新的（最大版本）。查询也应返回text列。

For a specific class_id (for example 10) I want to get all distinct type_ids with max version. There are 5 type_id 1,2,3,4,5 that may exists or not, however if a type_id exists in a specific class we want the latest (max version). The query should return the text column as well.

如果需要，这里是插入语句

In case it is needed, here are the insert statements

INSERT INTO `example1` (`id`,`version`,`text`,`class_id`,`tyoe_id`) VALUES (1,1,'text1',10,1);
INSERT INTO `example1` (`id`,`version`,`text`,`class_id`,`tyoe_id`) VALUES (2,1,'text2',10,2);
INSERT INTO `example1` (`id`,`version`,`text`,`class_id`,`tyoe_id`) VALUES (3,1,'test3',10,3);
INSERT INTO `example1` (`id`,`version`,`text`,`class_id`,`tyoe_id`) VALUES (4,1,'test4',10,4);
INSERT INTO `example1` (`id`,`version`,`text`,`class_id`,`tyoe_id`) VALUES (5,1,'test5',10,5);
INSERT INTO `example1` (`id`,`version`,`text`,`class_id`,`tyoe_id`) VALUES (6,2,'test44',10,3);
INSERT INTO `example1` (`id`,`version`,`text`,`class_id`,`tyoe_id`) VALUES (7,1,'1111',11,1);
INSERT INTO `example1` (`id`,`version`,`text`,`class_id`,`tyoe_id`) VALUES (8,1,'eferwer',12,2);
INSERT INTO `example1` (`id`,`version`,`text`,`class_id`,`tyoe_id`) VALUES (9,3,'last',10,3);
INSERT INTO `example1` (`id`,`version`,`text`,`class_id`,`tyoe_id`) VALUES (10,2,'new',10,5);
INSERT INTO `example1` (`id`,`version`,`text`,`class_id`,`tyoe_id`) VALUES (11,3,'rrrr',10,5);

答

在派生表中获取每个type_id的最大版本值

返回主表以获取对应的行。

尝试以下操作：

SELECT e.* 
FROM 
example1 AS e 
JOIN 
(
  SELECT type_id, 
         MAX(version) AS maximum_version
  FROM example1 
  WHERE class_id = 10
  GROUP BY type_id 
) AS dt ON dt.type_id = e.type_id AND 
           dt.maximum_version = e.version 
WHERE e.class_id = 10

结果

| id  | version | text  | class_id | type_id |
| --- | ------- | ----- | -------- | ------- |
| 1   | 1       | text1 | 10       | 1       |
| 2   | 1       | text2 | 10       | 2       |
| 4   | 1       | test4 | 10       | 4       |
| 9   | 3       | last  | 10       | 3       |
| 11  | 3       | rrrr  | 10       | 5       |

在数据库提琴上查看

MySQL语句选择特定列的最新条目

相关推荐