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

有一个表消息，其中包含如下所示的数据:

Id   Name   Other_Columns
-------------------------
1    A       A_data_1
2    A       A_data_2
3    A       A_data_3
4    B       B_data_1
5    B       B_data_2
6    C       C_data_1

如果我按名称从消息组中运行查询select *，我将得到如下结果:

1    A       A_data_1
4    B       B_data_1
6    C       C_data_1

哪个查询将返回以下结果?

3    A       A_data_3
5    B       B_data_2
6    C       C_data_1

也就是说，应该返回每个组中的最后一条记录。

目前，这是我使用的查询:

SELECT
  *
FROM (SELECT
  *
FROM messages
ORDER BY id DESC) AS x
GROUP BY name

但这看起来效率很低。还有其他方法可以达到同样的效果吗?

当前回答

Hi @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

2014-10-21 14:08:16

其他回答

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

SELECT 
  *
FROM 
  message 

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

2019-10-17 10:18:49

从MySQL 8.0.14开始，也可以使用横向派生表来实现:

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 < >小提琴

2021-11-11 10:02:52

试试这个:

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 )

2011-07-15 02:05:08

你可以通过计数来分组，也可以得到分组的最后一项，比如:

SELECT 
    user,
    COUNT(user) AS count,
    MAX(id) as last
FROM request 
GROUP BY user

2019-04-07 06:26:53

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演示

2021-04-06 20:58:26

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

推荐文章

最新文章

标签