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

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

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


当前回答

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

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

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

快乐编码,“愿原力与你同在”:)

其他回答

如果需要每个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小提琴

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

我在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`)

以下是我的解决方案:

SELECT 
  DISTINCT NAME,
  MAX(MESSAGES) OVER(PARTITION BY NAME) MESSAGES 
FROM MESSAGE;
SELECT * FROM table_name WHERE primary_key IN (SELECT MAX(primary_key) FROM table_name GROUP BY column_name )