原文地址：https://codedefault.com/s/how-can-i-retrieve-the-last-record-in-each-group-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
如果执行MySQL分组查询语句，如下：
select * from messages group by name
则会返回所有按name分组的第一条数据，如下：
1 A A_data_1
4 B B_data_1
6 C C_data_1
那么，如何查询按name分组后返回每组最后一条数据的MySQL语句呢，返回的结果如下：
3 A A_data_3
5 B B_data_2
6 C C_data_1
方案一
在MySQL 8.0版本以前，可以使用如下的语句：
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;
在MySQL 8.0版本中，MySQL新增了对窗口函数(Window Functions)的支持，我们可以使用窗口函数来简化SQL查询语句，并且不需要使用GROUP BY子句，如下：
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;
方案二
使用MySQL的IN(...)子句，如下：
SELECT id, name, other_columns
FROM messages
WHERE id IN (
SELECT MAX(id)
FROM messages
GROUP BY name
);
方案三
使用MySQL的GROUP_CONCAT和SUBSTRING_INDEX来查询，如下：
SELECT
`Id`,
`Name`,
SUBSTRING_INDEX(
GROUP_CONCAT(
`Other_Columns`
ORDER BY `Id` DESC
SEPARATOR '||'
),
'||',
1
) Other_Columns
FROM
messages
GROUP BY `Name`
实践代码：
1 SELECT * from( 2 SELECT 3 ( @i := CASE WHEN @pre_parent_code = sem_shop_wangwang THEN @i + 1 ELSE 1 END ) rownum,t_sem_logs.*, 4 ( @pre_parent_code := sem_shop_wangwang ) 5 FROM 6 t_sem_logs, 7 ( SELECT @i := 0, @pre_parent_code := '' ) AS b 8 where sem_edit_time <= '2018-07-13' 9 GROUP BY 10 sem_shop_wangwang,id 11 ORDER BY sem_shop_wangwang desc,DATE_FORMAT(sem_edit_time,'%Y-%m-%d %H:%i:%s') DESC,sem_agree_state ASC 12 ) a where rownum = 1 GROUP BY sem_agree_state order by DATE_FORMAT(sem_edit_time,'%Y-%m-%d %H:%m:%s') DESC; 13