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

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

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


当前回答

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

其他回答

这里有两个建议。首先,如果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;

我猜你说的"最后"是指最后一个。如果不是,则相应地更改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消息的消息。

解决方案由子查询小提琴链接

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

解决方案通过连接条件小提琴链接

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。

如果您需要分组查询中文本列的最新或最古老的记录,并且不希望使用子查询,您可以这样做…

例如,你有一个电影列表,需要获得系列电影和最新电影的数量

id series name
1 Star Wars A New hope
2 Star Wars The Empire Strikes Back
3 Star Wars Return of The Jedi
SELECT COUNT(id), series, SUBSTRING(MAX(CONCAT(id, name)), LENGTH(id) + 1), 
FROM Movies
GROUP BY series

这将返回……

id series name
3 Star Wars Return of The Jedi

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

比使用子查询更有效。

对于给定的例子:

SELECT MAX(Id), Name, SUBSTRING(MAX(CONCAT(Id, Other_Columns)), LENGTH(Id) + 1), 
FROM messages
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

另一种方法:

找到每个程序中最大m2_price的属性(一个程序中有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