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

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

试试这个:

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

通过这种方式，可以避免在子查询中进行相关子查询和/或排序，这往往非常缓慢/低效。

这里有两个建议。首先，如果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 *, max(id) from messages group by name 

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

你也可以从这里看。

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 ;

另一种方法:

找到每个程序中最大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