mysql分组区某列最后一条_[MySQL]MySQL数据库如何按某列分组排序后查询每个分组的最后一条数据？...

比如当前有如下的消息表(messages)及示例数据：

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

按照以下SQL语句查询：

select * from messages group by name

得到的查询结果为：

1 A A_data_1

4 B B_data_1

6 C C_data_1

如何使用SQL语句查询每个分组的最后一条数据，如：

3 A A_data_3

5 B B_data_2

6 C C_data_1

方案一SELECT m1.*

FROM messages m1 LEFT JOIN messages m2

ON (m1.name = m2.name AND m1.id < m2.id)

WHERE m2.id IS NULL;

如果中MySQL 8.0中，可以使用 WITH 的语法(WITH语法文档)，具体实现SQL语句如下：

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;

方案二

使用子查询

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

方案三SELECT

Id, Name, OtherColumns

FROM messages

WHERE NOT EXISTS (

SELECT * FROM messages as M2

WHERE M2.Name = messages.Name

AND M2.Id > messages.Id

)

方案四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 )

方案五SELECT

`Id`,

`Name`,

SUBSTRING_INDEX(

GROUP_CONCAT(

`Other_Columns`

ORDER BY `Id` DESC

SEPARATOR '||'

),

'||',

1

) Other_Columns

FROM

messages

GROUP BY `Name`