检索每个组中的最后一条记录

o

outis

MySQL 8.0 现在支持 windowing functions，就像几乎所有流行的 SQL 实现一样。使用这个标准语法，我们可以编写每个组的最大 n 个查询：

WITH ranked_messages AS (
  SELECT m.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) AS rn
  FROM messages AS m
)
SELECT * FROM ranked_messages WHERE rn = 1;

MySQL 手册中说明了查找 groupwise maximal rows 的这种方法和其他方法。

以下是我在 2009 年为这个问题写的原始答案：

我这样写解决方案：

SELECT m1.*
FROM messages m1 LEFT JOIN messages m2
 ON (m1.name = m2.name AND m1.id < m2.id)
WHERE m2.id IS NULL;

关于性能，一种或另一种解决方案可能会更好，具体取决于数据的性质。因此，您应该测试这两个查询并使用给定数据库性能更好的查询。

例如，我有一份 StackOverflow August data dump。我将使用它进行基准测试。 Posts 表中有 1,114,357 行。这是在我的 Macbook Pro 2.40GHz 上的 MySQL 5.0.75 上运行的。

我将编写一个查询来查找给定用户 ID（我的）的最新帖子。

首先在子查询中使用 @Eric 的技术 shown 和 GROUP BY：

SELECT p1.postid
FROM Posts p1
INNER JOIN (SELECT pi.owneruserid, MAX(pi.postid) AS maxpostid
            FROM Posts pi GROUP BY pi.owneruserid) p2
  ON (p1.postid = p2.maxpostid)
WHERE p1.owneruserid = 20860;

1 row in set (1 min 17.89 sec)

即使是 EXPLAIN analysis 也需要超过 16 秒：

+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
| id | select_type | table      | type   | possible_keys              | key         | key_len | ref          | rows    | Extra       |
+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                       | NULL        | NULL    | NULL         |   76756 |             | 
|  1 | PRIMARY     | p1         | eq_ref | PRIMARY,PostId,OwnerUserId | PRIMARY     | 8       | p2.maxpostid |       1 | Using where | 
|  2 | DERIVED     | pi         | index  | NULL                       | OwnerUserId | 8       | NULL         | 1151268 | Using index | 
+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
3 rows in set (16.09 sec)

现在使用 my technique 和 LEFT JOIN 生成相同的查询结果：

SELECT p1.postid
FROM Posts p1 LEFT JOIN posts p2
  ON (p1.owneruserid = p2.owneruserid AND p1.postid < p2.postid)
WHERE p2.postid IS NULL AND p1.owneruserid = 20860;

1 row in set (0.28 sec)

EXPLAIN 分析显示两个表都能够使用它们的索引：

+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
| id | select_type | table | type | possible_keys              | key         | key_len | ref   | rows | Extra                                |
+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
|  1 | SIMPLE      | p1    | ref  | OwnerUserId                | OwnerUserId | 8       | const | 1384 | Using index                          | 
|  1 | SIMPLE      | p2    | ref  | PRIMARY,PostId,OwnerUserId | OwnerUserId | 8       | const | 1384 | Using where; Using index; Not exists | 
+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
2 rows in set (0.00 sec)

这是我的 Posts 表的 DDL：

CREATE TABLE `posts` (
  `PostId` bigint(20) unsigned NOT NULL auto_increment,
  `PostTypeId` bigint(20) unsigned NOT NULL,
  `AcceptedAnswerId` bigint(20) unsigned default NULL,
  `ParentId` bigint(20) unsigned default NULL,
  `CreationDate` datetime NOT NULL,
  `Score` int(11) NOT NULL default '0',
  `ViewCount` int(11) NOT NULL default '0',
  `Body` text NOT NULL,
  `OwnerUserId` bigint(20) unsigned NOT NULL,
  `OwnerDisplayName` varchar(40) default NULL,
  `LastEditorUserId` bigint(20) unsigned default NULL,
  `LastEditDate` datetime default NULL,
  `LastActivityDate` datetime default NULL,
  `Title` varchar(250) NOT NULL default '',
  `Tags` varchar(150) NOT NULL default '',
  `AnswerCount` int(11) NOT NULL default '0',
  `CommentCount` int(11) NOT NULL default '0',
  `FavoriteCount` int(11) NOT NULL default '0',
  `ClosedDate` datetime default NULL,
  PRIMARY KEY  (`PostId`),
  UNIQUE KEY `PostId` (`PostId`),
  KEY `PostTypeId` (`PostTypeId`),
  KEY `AcceptedAnswerId` (`AcceptedAnswerId`),
  KEY `OwnerUserId` (`OwnerUserId`),
  KEY `LastEditorUserId` (`LastEditorUserId`),
  KEY `ParentId` (`ParentId`),
  CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`PostTypeId`) REFERENCES `posttypes` (`PostTypeId`)
) ENGINE=InnoDB;

评论者注意：如果您想要另一个具有不同版本的 MySQL、不同的数据集或不同的表设计的基准测试，请随意自己做。我已经展示了上面的技术。 Stack Overflow 在这里向您展示如何进行软件开发工作，而不是为您完成所有工作。

真的吗？如果您有大量条目会发生什么？例如，如果您正在使用内部版本控制，例如，并且每个文件有大量版本，那么连接结果将是巨大的。你有没有用这个方法对子查询方法进行基准测试？我很想知道哪个会赢，但也没有好奇到不先问你。

您能否详细说明条件“WHERE p2.postid IS NULL”的目的？它不会与其他条件“p1.postid < p2.postid”相矛盾吗？

@KatherineChen，它与 LEFT [OUTER] JOIN 的工作方式有关。如果该连接在 m1 中找不到给定行的匹配项，那么它仍将返回该行 m1，但 m2 的所有列都将为 NULL。

@KatherineChen，我将其描述为：没有找到具有相同 name 和更大 id 的其他行，因此对于给定的 name 值，m1 必须是具有最大 id 的行。

@ysth 我希望 Stack Overflow 的重点是向读者展示技术，这样他们就可以自己做更多的工作。目标不是为他们做所有的工作。

R

Rick James

UPD：2017-03-31，MySQL 的版本 5.7.5 默认启用 ONLY_FULL_GROUP_BY 开关（因此，非确定性 GROUP BY 查询被禁用）。此外，他们更新了 GROUP BY 实现，即使使用禁用的开关，该解决方案也可能不再按预期工作。需要检查。

Bill Karwin 的上述解决方案在组内的项目计数相当少时工作得很好，但当组相当大时查询的性能会变差，因为该解决方案只需要 n*n/2 + n/2 个比较，而只有 IS NULL 个比较。

我在包含 1182 个组的 18684446 行的 InnoDB 表上进行了测试。该表包含功能测试的测试结果，并以 (test_id, request_id) 作为主键。因此，test_id 是一个组，我正在为每个 test_id 搜索最后一个 request_id。

Bill 的解决方案已经在我的 dell e4310 上运行了几个小时，即使它在覆盖索引上运行（因此 EXPLAIN 中的 using index），我也不知道它何时会完成。

我有几个基于相同想法的其他解决方案：

如果基础索引是 BTREE 索引（通常是这种情况），则最大的 (group_id, item_value) 对是每个 group_id 中的最后一个值，如果我们按降序遍历索引，则这是每个 group_id 的第一个值；

如果我们读取索引覆盖的值，则按索引的顺序读取值；

每个索引都隐含地包含附加到该索引的主键列（即主键在覆盖索引中）。在下面的解决方案中，我直接对主键进行操作，在您的情况下，您只需要在结果中添加主键列。

在许多情况下，在子查询中按所需顺序收集所需的行 id 并在 id 上加入子查询的结果要便宜得多。由于对于子查询结果中的每一行，MySQL 将需要基于主键进行一次提取，因此子查询将首先放入连接中，并且行将按子查询中的 id 顺序输出（如果我们省略显式 ORDER BY加入）

3 ways MySQL uses indexes 是了解一些细节的好文章。

解决方案 1

这个速度非常快，在我的 18M+ 行上大约需要 0.8 秒：

SELECT test_id, MAX(request_id) AS request_id
FROM testresults
GROUP BY test_id DESC;

如果要将顺序更改为 ASC，请将其放入子查询中，仅返回 id 并将其用作子查询以加入其余列：

SELECT test_id, request_id
FROM (
    SELECT test_id, MAX(request_id) AS request_id
    FROM testresults
    GROUP BY test_id DESC) as ids
ORDER BY test_id;

这对我的数据大约需要 1.2 秒。

解决方案 2

这是我的表需要大约 19 秒的另一个解决方案：

SELECT test_id, request_id
FROM testresults, (SELECT @group:=NULL) as init
WHERE IF(IFNULL(@group, -1)=@group:=test_id, 0, 1)
ORDER BY test_id DESC, request_id DESC

它也以降序返回测试。由于它执行完整的索引扫描，因此速度要慢得多，但它在这里让您了解如何为每个组输出 N max 行。

查询的缺点是它的结果不能被查询缓存缓存。

E

Eric

使用您的 subquery 返回正确的分组，因为您已经完成了一半。

尝试这个：

select
    a.*
from
    messages a
    inner join 
        (select name, max(id) as maxid from messages group by name) as b on
        a.id = b.maxid

如果不是 id，您需要以下最大值：

select
    a.*
from
    messages a
    inner join 
        (select name, max(other_col) as other_col 
         from messages group by name) as b on
        a.name = b.name
        and a.other_col = b.other_col

这样，您可以避免相关子查询和/或子查询中的排序，这往往非常缓慢/低效。

J

JYelton

我找到了一个不同的解决方案，即获取每个组中最后一个帖子的 ID，然后使用第一个查询的结果作为 WHERE x IN 构造的参数从消息表中进行选择：

SELECT id, name, other_columns
FROM messages
WHERE id IN (
    SELECT MAX(id)
    FROM messages
    GROUP BY name
);

与其他一些解决方案相比，我不知道它的性能如何，但它对我的表有 3+ 百万行的效果非常好。（4 秒执行，1200 多个结果）

这应该适用于 MySQL 和 SQL Server。

此解决方案使 mysql 服务器/服务崩溃。我已经检查了 1000 万条记录，不推荐此解决方案。在这种情况下使用 IN 是非常糟糕的。

@Kamlesh 也许您缺少一些索引？这个解决方案也有近 10 年的历史了，也许一些更新改变了这个查询的行为或性能。

漂亮而优雅的解决方案。即使没有唯一的排序属性，只需稍加改进即可使其工作。 SELECT not_univoque_id, name, other_columns FROM messages WHERE (name, not_univoque_id) IN (SELECT name, MAX(not_univoque_id) FROM messages GROUP BY name);

这甚至适用于 Firebird 1.0.3！

C

Community

通过子查询 fiddle Link 解决

select * from messages where id in
(select max(id) from messages group by Name)

解决方案通过加入条件 fiddle link

select m1.* from messages m1 
left outer join messages m2 
on ( m1.id<m2.id and m1.name=m2.name )
where m2.id is null

这篇文章的原因是只提供小提琴链接。其他答案中已经提供了相同的 SQL。

S

Song Zhengyi

一种速度相当快的方法如下。

SELECT * 
FROM messages a
WHERE Id = (SELECT MAX(Id) FROM messages WHERE a.Name = Name)

结果

Id  Name    Other_Columns
3   A   A_data_3
5   B   B_data_2
6   C   C_data_1

这甚至适用于 Firebird 1.0.3！ ...并且似乎比stackoverflow.com/a/9368897/2932052“更快”

S

Steve Kass

这里有两个建议。首先，如果mysql支持ROW_NUMBER()，就很简单了：

WITH Ranked AS (
  SELECT Id, Name, OtherColumns,
    ROW_NUMBER() OVER (
      PARTITION BY Name
      ORDER BY Id DESC
    ) AS rk
  FROM messages
)
  SELECT Id, Name, OtherColumns
  FROM messages
  WHERE rk = 1;

我假设“最后一个”是指按 ID 顺序排列的最后一个。如果不是，请相应地更改 ROW_NUMBER() 窗口的 ORDER BY 子句。如果 ROW_NUMBER() 不可用，这是另一种解决方案：

其次，如果没有，这通常是一个很好的方法：

SELECT
  Id, Name, OtherColumns
FROM messages
WHERE NOT EXISTS (
  SELECT * FROM messages as M2
  WHERE M2.Name = messages.Name
  AND M2.Id > messages.Id
)

换言之，选择没有具有相同名称的 later-Id 消息的消息。

MySQL 不支持 ROW_NUMBER() 或 CTE。

MySQL 8.0（和 MariaDB 10.2）现在支持 ROW_NUMBER() 和 CTE。

也许通过使用两个别名（a 和 b）可以提高可读性，例如 SELECT * FROM messages a WHERE NOT EXISTS (SELECT * FROM messages as b WHERE a.Name = b.Name AND a.Id > b.Id)

第一个版本如何更好？第二个非常清晰和快速。

Y

Yagnesh bhalala

我们将了解如何使用 MySQL 获取 Group By 记录中的最后一条记录。例如，如果您有此结果集的帖子。

id category_id post_title

1 1 Title 1

2 1 Title 2

3 1 Title 3

4 2 Title 4

5 2 Title 5

6 3 Title 6

我希望能够获得每个类别中的最后一篇文章，即标题 3、标题 5 和标题 6。要按类别获取文章，您将使用 MySQL Group By 键盘。

select * from posts group by category_id

但是我们从这个查询中得到的结果是。

id category_id post_title

1 1 Title 1

4 2 Title 4

6 3 Title 6

group by 将始终返回结果集上组中的第一条记录。

SELECT id, category_id, post_title FROM posts WHERE id IN ( SELECT MAX(id) FROM posts GROUP BY category_id );

这将返回每个组中 ID 最高的帖子。

id category_id post_title

3 1 Title 3

5 2 Title 5

6 3 Title 6

Reference Click Here

Y

Yoseph

显然有很多不同的方法可以获得相同的结果，你的问题似乎是在 MySQL 中获得每个组的最后结果的有效方法。如果您正在处理大量数据并假设您正在使用 InnoDB，即使是最新版本的 MySQL（例如 5.7.21 和 8.0.4-rc），那么可能没有有效的方法来执行此操作。

我们有时需要对超过 6000 万行的表执行此操作。

对于这些示例，我将使用只有大约 150 万行的数据，其中查询需要查找数据中所有组的结果。在我们的实际案例中，我们通常需要返回大约 2,000 个组的数据（假设不需要检查太多数据）。

我将使用以下表格：

CREATE TABLE temperature(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
  groupID INT UNSIGNED NOT NULL, 
  recordedTimestamp TIMESTAMP NOT NULL, 
  recordedValue INT NOT NULL,
  INDEX groupIndex(groupID, recordedTimestamp), 
  PRIMARY KEY (id)
);

CREATE TEMPORARY TABLE selected_group(id INT UNSIGNED NOT NULL, PRIMARY KEY(id));

温度表包含大约 150 万条随机记录，以及 100 个不同的组。 selected_group 填充了这 100 个组（在我们的例子中，这通常小于所有组的 20%）。

由于此数据是随机的，这意味着多行可以具有相同的记录时间戳。我们想要的是按照 groupID 的顺序获取所有选定组的列表，其中包含每个组的最后记录的时间戳，如果同一个组有多个这样的匹配行，那么这些行的最后一个匹配 id。

如果假设 MySQL 有一个 last() 函数，它在特殊的 ORDER BY 子句中从最后一行返回值，那么我们可以简单地做：

SELECT 
  last(t1.id) AS id, 
  t1.groupID, 
  last(t1.recordedTimestamp) AS recordedTimestamp, 
  last(t1.recordedValue) AS recordedValue
FROM selected_group g
INNER JOIN temperature t1 ON t1.groupID = g.id
ORDER BY t1.recordedTimestamp, t1.id
GROUP BY t1.groupID;

在这种情况下只需要检查 100 行，因为它不使用任何正常的 GROUP BY 函数。这将在 0 秒内执行，因此效率很高。请注意，通常在 MySQL 中，我们会在 GROUP BY 子句之后看到 ORDER BY 子句，但是这个 ORDER BY 子句用于确定 last() 函数的 ORDER，如果它在 GROUP BY 之后，那么它将对 GROUPS 进行排序。如果不存在 GROUP BY 子句，则所有返回行中的最后一个值将相同。

然而 MySQL 没有这个，所以让我们看看它有什么的不同想法，并证明这些都不是有效的。

示例 1

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM selected_group g
INNER JOIN temperature t1 ON t1.id = (
  SELECT t2.id
  FROM temperature t2 
  WHERE t2.groupID = g.id
  ORDER BY t2.recordedTimestamp DESC, t2.id DESC
  LIMIT 1
);

这检查了 3,009,254 行，在 5.7.21 上花费了约 0.859 秒，在 8.0.4-rc 上稍长一些

示例 2

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue 
FROM temperature t1
INNER JOIN ( 
  SELECT max(t2.id) AS id   
  FROM temperature t2
  INNER JOIN (
    SELECT t3.groupID, max(t3.recordedTimestamp) AS recordedTimestamp
    FROM selected_group g
    INNER JOIN temperature t3 ON t3.groupID = g.id
    GROUP BY t3.groupID
  ) t4 ON t4.groupID = t2.groupID AND t4.recordedTimestamp = t2.recordedTimestamp
  GROUP BY t2.groupID
) t5 ON t5.id = t1.id;

这检查了 1,505,331 行，在 5.7.21 上花费了大约 1.25 秒，在 8.0.4-rc 上花费了稍长的时间

示例 3

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue 
FROM temperature t1
WHERE t1.id IN ( 
  SELECT max(t2.id) AS id   
  FROM temperature t2
  INNER JOIN (
    SELECT t3.groupID, max(t3.recordedTimestamp) AS recordedTimestamp
    FROM selected_group g
    INNER JOIN temperature t3 ON t3.groupID = g.id
    GROUP BY t3.groupID
  ) t4 ON t4.groupID = t2.groupID AND t4.recordedTimestamp = t2.recordedTimestamp
  GROUP BY t2.groupID
)
ORDER BY t1.groupID;

这检查了 3,009,685 行，在 5.7.21 上花费了大约 1.95 秒，在 8.0.4-rc 上稍长一些

示例 4

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM selected_group g
INNER JOIN temperature t1 ON t1.id = (
  SELECT max(t2.id)
  FROM temperature t2 
  WHERE t2.groupID = g.id AND t2.recordedTimestamp = (
      SELECT max(t3.recordedTimestamp)
      FROM temperature t3 
      WHERE t3.groupID = g.id
    )
);

这检查了 6,137,810 行，在 5.7.21 上花费了约 2.2 秒，在 8.0.4-rc 上稍长一些

示例 5

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM (
  SELECT 
    t2.id, 
    t2.groupID, 
    t2.recordedTimestamp, 
    t2.recordedValue, 
    row_number() OVER (
      PARTITION BY t2.groupID ORDER BY t2.recordedTimestamp DESC, t2.id DESC
    ) AS rowNumber
  FROM selected_group g 
  INNER JOIN temperature t2 ON t2.groupID = g.id
) t1 WHERE t1.rowNumber = 1;

这检查了 6,017,808 行，在 8.0.4-rc 上花费了大约 4.2 秒

例 6

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue 
FROM (
  SELECT 
    last_value(t2.id) OVER w AS id, 
    t2.groupID, 
    last_value(t2.recordedTimestamp) OVER w AS recordedTimestamp, 
    last_value(t2.recordedValue) OVER w AS recordedValue
  FROM selected_group g
  INNER JOIN temperature t2 ON t2.groupID = g.id
  WINDOW w AS (
    PARTITION BY t2.groupID 
    ORDER BY t2.recordedTimestamp, t2.id 
    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  )
) t1
GROUP BY t1.groupID;

这检查了 6,017,908 行，在 8.0.4-rc 上花费了大约 17.5 秒

例 7

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue 
FROM selected_group g
INNER JOIN temperature t1 ON t1.groupID = g.id
LEFT JOIN temperature t2 
  ON t2.groupID = g.id 
  AND (
    t2.recordedTimestamp > t1.recordedTimestamp 
    OR (t2.recordedTimestamp = t1.recordedTimestamp AND t2.id > t1.id)
  )
WHERE t2.id IS NULL
ORDER BY t1.groupID;

这个是永远的，所以我不得不杀了它。

这是一个不同的问题。解决方案是一个巨大的 UNION ALL 查询。

@PaulSpiegel 我猜你是在拿巨大的 UNION ALL 开玩笑。除了需要提前知道所有选定的组这一事实之外，如果有 2,000 个选定的组，这将是一个非常庞大的查询，它的性能甚至会比上面最快的示例更差，所以不，这不是一个解决方案。

我绝对是认真的。过去我已经用几百组测试过。当您需要处理大组中的关系时，UNION ALL 是 MySQL 中强制执行最佳执行计划的唯一方法。 SELECT DISTINCT(groupID) 速度很快，会为您提供构建此类查询所需的所有数据。查询大小应该没问题，只要它不超过 max_allowed_packet，在 MySQL 5.7 中默认为 4MB。

M

M Khalid Junaid

这是另一种使用带有 order by 的 GROUP_CONCAT 和 SUBSTRING_INDEX 从列表中选择一条记录来获取最后一条相关记录的方法

SELECT 
  `Id`,
  `Name`,
  SUBSTRING_INDEX(
    GROUP_CONCAT(
      `Other_Columns` 
      ORDER BY `Id` DESC 
      SEPARATOR '||'
    ),
    '||',
    1
  ) Other_Columns 
FROM
  messages 
GROUP BY `Name`

上面的查询将对同一 Name 组中的所有 Other_Columns 进行分组，并且使用 ORDER BY id DESC 将使用提供的分隔符按降序加入特定组中的所有 Other_Columns，在我使用 || 的情况下, 在此列表上使用 SUBSTRING_INDEX 将选择第一个

小提琴演示

请注意，group_concat_max_len 限制了您可以处理的行数。

S

Shai

我还没有测试过大型数据库，但我认为这可能比加入表更快：

SELECT *, Max(Id) FROM messages GROUP BY Name

这将返回任意数据。换句话说，返回的列可能不是来自具有 MAX(Id) 的记录。

从具有 WHERE 条件的一组记录中选择最大 Id 很有用：“SELECT Max(Id) FROM Prod WHERE Pn='”+ Pn +“'” 它从具有相同 Pn 的一组记录中返回最大 Id.In c#使用 reader.GetString(0) 获取结果

为什么这篇文章首先获得了正面投票？在我看来，它完全没有抓住重点。

M

M Khalid Junaid

SELECT 
  column1,
  column2 
FROM
  table_name 
WHERE id IN 
  (SELECT 
    MAX(id) 
  FROM
    table_name 
  GROUP BY column1) 
ORDER BY column1 ;

你能详细说明你的答案吗？为什么您的查询优于 Vijay 的原始查询？

b

bikashphp

嗨@Vijay Dev，如果您的表消息包含自动递增主键的Id，那么要根据主键获取最新记录，您的查询应如下所示：

SELECT m1.* FROM messages m1 INNER JOIN (SELECT max(Id) as lastmsgId FROM messages GROUP BY Name) m2 ON m1.Id=m2.lastmsgId

这是我发现的最快的

这也是一个很好的 b/c 限制，并且可以在子查询中使用偏移量（或者在连接中使用查询时调用的任何内容）。 MySQL 不允许在典型的子查询中进行限制/偏移，但它们允许用于这样的连接。

S

Shrikant Gupta

您也可以从这里观看。

http://sqlfiddle.com/#!9/ef42b/9

第一个解决方案

SELECT d1.ID,Name,City FROM Demo_User d1
INNER JOIN
(SELECT MAX(ID) AS ID FROM Demo_User GROUP By NAME) AS P ON (d1.ID=P.ID);

第二个解决方案

SELECT * FROM (SELECT * FROM Demo_User ORDER BY ID DESC) AS T GROUP BY NAME ;

S

Simon

有什么方法可以使用此方法删除表中的重复项？结果集基本上是唯一记录的集合，所以如果我们可以删除所有不在结果集中的记录，我们实际上就没有重复了吗？我试过这个，但 mySQL 给出了 1093 错误。

DELETE FROM messages WHERE id NOT IN
 (SELECT m1.id  
 FROM messages m1 LEFT JOIN messages m2  
 ON (m1.name = m2.name AND m1.id < m2.id)  
 WHERE m2.id IS NULL)

有没有办法将输出保存到临时变量，然后从 NOT IN（临时变量）中删除？ @Bill 感谢您提供非常有用的解决方案。

编辑：认为我找到了解决方案：

DROP TABLE IF EXISTS UniqueIDs; 
CREATE Temporary table UniqueIDs (id Int(11)); 

INSERT INTO UniqueIDs 
    (SELECT T1.ID FROM Table T1 LEFT JOIN Table T2 ON 
    (T1.Field1 = T2.Field1 AND T1.Field2 = T2.Field2 #Comparison Fields  
    AND T1.ID < T2.ID) 
    WHERE T2.ID IS NULL); 

DELETE FROM Table WHERE id NOT IN (SELECT ID FROM UniqueIDs);

有趣是因为相关，但这不值得自己进行问答吗？

B

Brock Adams

尝试这个：

SELECT jos_categories.title AS name,
       joined .catid,
       joined .title,
       joined .introtext
FROM   jos_categories
       INNER JOIN (SELECT *
                   FROM   (SELECT `title`,
                                  catid,
                                  `created`,
                                  introtext
                           FROM   `jos_content`
                           WHERE  `sectionid` = 6
                           ORDER  BY `id` DESC) AS yes
                   GROUP  BY `yes`.`catid` DESC
                   ORDER  BY `yes`.`created` DESC) AS joined
         ON( joined.catid = jos_categories.id )

P

Paul Roub

这是我的解决方案：

SELECT 
  DISTINCT NAME,
  MAX(MESSAGES) OVER(PARTITION BY NAME) MESSAGES 
FROM MESSAGE;

这不会返回每个名称的最新消息。它只是 SELECT NAME, MAX(MESSAGES) MESSAGES FROM MESSAGE GROUP BY NAME 的一个过于复杂的版本。

此外，这个公式是非常低效的。

S

S.S. Anne

SELECT * FROM table_name WHERE primary_key IN (SELECT MAX(primary_key) FROM table_name GROUP BY column_name )

A

Abhishek Sengupta

**

嗨，这个查询可能会有所帮助：

**

SELECT 
  *
FROM 
  message 

WHERE 
  `Id` IN (
    SELECT 
      MAX(`Id`) 
    FROM 
      message 
    GROUP BY 
      `Name`
  ) 
ORDER BY 
   `Id` DESC

m

milad

我在 https://dzone.com/articles/get-last-record-in-each-mysql-group 中找到了最佳解决方案

select * from `data` where `id` in (select max(`id`) from `data` group by `name_id`)

您没有在已发布的答案中看到此查询吗？

a

animuson

根据您的问题，以下查询将正常工作。

SELECT M1.* 
FROM MESSAGES M1,
(
 SELECT SUBSTR(Others_data,1,2),MAX(Others_data) AS Max_Others_data
 FROM MESSAGES
 GROUP BY 1
) M2
WHERE M1.Others_data = M2.Max_Others_data
ORDER BY Others_data;

U

Ullas

如果您想要每个 Name 的最后一行，则可以通过 Name 为每个行组指定行号，并按 Id 降序排列。

询问

SELECT t1.Id, 
       t1.Name, 
       t1.Other_Columns
FROM 
(
     SELECT Id, 
            Name, 
            Other_Columns,
    (
        CASE Name WHEN @curA 
        THEN @curRow := @curRow + 1 
        ELSE @curRow := 1 AND @curA := Name END 
    ) + 1 AS rn 
    FROM messages t, 
    (SELECT @curRow := 0, @curA := '') r 
    ORDER BY Name,Id DESC 
)t1
WHERE t1.rn = 1
ORDER BY t1.Id;

SQL小提琴

A

Azathoth

这个怎么样：

SELECT DISTINCT ON (name) *
FROM messages
ORDER BY name, id DESC;

我有类似的问题（在 postgresql 上）和 1M 记录表。这个解决方案需要 1.7 秒，而使用 LEFT JOIN 的解决方案需要 44 秒。在我的情况下，我必须根据 NULL 值过滤您的 name 字段的对应项，从而使性能提高 0.2 秒

m

michal.jakubeczy

如果性能确实是您关心的问题，您可以在表中引入一个名为 IsLastInGroup 的新列，类型为 BIT。

在最后的列上将其设置为 true，并在每行插入/更新/删除时对其进行维护。写入会更慢，但你会从读取中受益。这取决于您的用例，我仅在您专注于阅读时才推荐它。

因此，您的查询将如下所示：

SELECT * FROM Messages WHERE IsLastInGroup = 1

Moodle 中的一些表有这样的标志列。

J

Jacek Błocki

关于什么：

select *, max(id) from messages group by name

我已经在 sqlite 上对其进行了测试，它返回所有名称的所有列和最大 id 值。

首先，您的答案通常不能为 MySQL 提供正确的结果，因为最大 ID 和其余列可能来自同一组的不同记录。其次，与 9 年前一样，相同的答案已经存在（stackoverflow.com/a/9956875/1089242）。第三，问题具体提到了MySQL。如果您关心的是通用 sql 数据库（或只是 sqlite），请查看其他问题，例如 stackoverflow.com/q/3800551/1089242

我只用 sqlite 测试过它，它产生了正确的结果。我还没有尝试过MySQL。解决方案很简单，所以我把它贴在这里。有时简单是错误的。你能分享一个不工作的sqlite例子吗？

@JacekBłocki 这个问题是关于 MySQL 的。您的答案可能在 sqlite 中有效（我不知道，我没有），但在 MySQL 中无效。

L

Lukasz Szozda

MariaDB 10.3 和更高版本使用 GROUP_CONCAT。

这个想法是使用 ORDER BY + LIMIT：

SELECT GROUP_CONCAT(id ORDER BY id DESC LIMIT 1) AS id,
       name,
       GROUP_CONCAT(Other_columns ORDER BY id DESC LIMIT 1) AS Other_columns
FROM t
GROUP BY name;

db<>fiddle demo

佚

佚名

如果您需要分组查询中文本列的最新或最旧记录，并且您不想使用子查询，您可以这样做...

前任。您有一个电影列表，需要获取该系列和最新电影的计数

id 系列名称 1 星球大战新希望 2 星球大战帝国反击战 3 星球大战绝地归来

SELECT COUNT(id), series, SUBSTRING(MAX(CONCAT(id, name)), LENGTH(id) + 1), 
FROM Movies
GROUP BY series

这返回...

id系列名称3星球大战绝地归来

MAX 将返回具有最高值的行，因此通过将 id 连接到名称，您现在将获得最新记录，然后去掉 id 以获得最终结果。

比使用子查询更有效。

所以对于给定的例子：

SELECT MAX(Id), Name, SUBSTRING(MAX(CONCAT(Id, Other_Columns)), LENGTH(Id) + 1), 
FROM messages
GROUP BY Name

快乐的编码，和“愿原力与你同在”:)

这可能行不通，让我们假设在您的示例中“新希望”的 id 是 9，“绝地归来”的 id 是 10。这是因为 MAX 函数用于连接 id 和 name它在一个字符串上。因此，“9A 新希望”将比“10 绝地归来”具有更高的顺序

C

Calos

希望下面的Oracle查询可以提供帮助：

WITH Temp_table AS
(
    Select id, name, othercolumns, ROW_NUMBER() over (PARTITION BY name ORDER BY ID 
    desc)as rank from messages
)
Select id, name,othercolumns from Temp_table where rank=1

K

Ka.

另一种方法：

在每个程序中找到具有最大 m2_price 的属性（1 个程序中的 n 个属性）：

select * from properties p
join (
    select max(m2_price) as max_price 
    from properties 
    group by program_id
) p2 on (p.program_id = p2.program_id)
having p.m2_price = max_price

i

id'7238

从 MySQL 8.0.14 开始，这也可以使用 Lateral Derived Tables 来实现：

SELECT t.*
FROM messages t
JOIN LATERAL (
  SELECT name, MAX(id) AS id 
  FROM messages t1
  WHERE t.name = t1.name
  GROUP BY name
) trn ON t.name = trn.name AND t.id = trn.id

db<>fiddle

检索每个组中的最后一条记录 - MySQL

关注公众号

想领先一步获取最新的外包任务吗？

相似问题

平台

支持

联系我们